Migrating from ASE to postgreSQL

[themadmurph]

I've got a model which was originally in ASE for production. I need to convert to PG but some of the table names are being truncated. I don't know if this is an issue with the pg9.5 driver (I'm using PG10, where I can create the full length table names).

Is there a way in PD16.5 to configure maximum length on the table name when it is generated?

[gtss]

Post by themadmurph
I've got a model which was originally in ASE for production. I need to convert to PG but some of the table names are being truncated. I don't know if this is an issue with the pg9.5 driver (I'm using PG10, where I can create the full length table names).
Is there a way in PD16.5 to configure maximum length on the table name when it is generated?

Yes there is.

Open your physical model, make sure you have the current DBMS set to a PG-compatible one. I'm sure you already have this; just mentioning for completeness.

Choose "Tools \ Model Options" from the main menu, then in the dialog that opens up choose "Table" from under the "Naming Convention", then go to the "Code" tab.
The "Maximum length" setting is there.


And everything else you always wanted but were afraid to ask :-)

[themadmurph]

Post by gtss

Post by themadmurph
I've got a model which was originally in ASE for production. I need to convert to PG but some of the table names are being truncated. I don't know if this is an issue with the pg9.5 driver (I'm using PG10, where I can create the full length table names).
Is there a way in PD16.5 to configure maximum length on the table name when it is generated?

Yes there is.
Open your physical model, make sure you have the current DBMS set to a PG-compatible one. I'm sure you already have this; just mentioning for completeness.
Choose "Tools \ Model Options" from the main menu, then in the dialog that opens up choose "Table" from under the "Naming Convention", then go to the "Code" tab.
The "Maximum length" setting is there.
And everything else you always wanted but were afraid to ask :-)

Thanks for the reply. I tried that, which is why I thought it was a driver issue.

My DBMS is set to PotgreSQL 9.x

My table name is Air_Quality_Urban_Observatory_Mapping, but even after checking the setting you mentioned (which is at 254 and I over-typed to make sure), I still get -:
Air_Quality_Urban_Observatory_M (
in the preview window!

Any other thoughts/suggestions welcome please?

[Laura]

Post by themadmurph

Post by gtss

Post by themadmurph
I've got a model which was originally in ASE for production. I need to convert to PG but some of the table names are being truncated. I don't know if this is an issue with the pg9.5 driver (I'm using PG10, where I can create the full length table names).
Is there a way in PD16.5 to configure maximum length on the table name when it is generated?

Yes there is.
Open your physical model, make sure you have the current DBMS set to a PG-compatible one. I'm sure you already have this; just mentioning for completeness.
Choose "Tools \ Model Options" from the main menu, then in the dialog that opens up choose "Table" from under the "Naming Convention", then go to the "Code" tab.
The "Maximum length" setting is there.
And everything else you always wanted but were afraid to ask :-)

Thanks for the reply. I tried that, which is why I thought it was a driver issue.
My DBMS is set to PotgreSQL 9.x
Air_Quality_Urban_Observatory_M (
in the preview window!
Any other thoughts/suggestions welcome please?

Have you checked the settings in your XDB file?

Go to Database\Edit Current DBMS... and expand the Scripts\Objects\Table node and scroll down to the Maxlen property (second from the bottom). It's set to 31 in my PD 16.1 PostgreSQL 8 XDB, which matches the length of your truncated value, so I suspect that's it. (Though of course I would recommend backing up your original XDB file prior to making any changes)

[themadmurph]

Post by Laura

Post by themadmurph

Post by gtss

Post by themadmurph
I've got a model which was originally in ASE for production. I need to convert to PG but some of the table names are being truncated. I don't know if this is an issue with the pg9.5 driver (I'm using PG10, where I can create the full length table names).
Is there a way in PD16.5 to configure maximum length on the table name when it is generated?

Yes there is.
Open your physical model, make sure you have the current DBMS set to a PG-compatible one. I'm sure you already have this; just mentioning for completeness.
Choose "Tools \ Model Options" from the main menu, then in the dialog that opens up choose "Table" from under the "Naming Convention", then go to the "Code" tab.
The "Maximum length" setting is there.
And everything else you always wanted but were afraid to ask :-)

Thanks for the reply. I tried that, which is why I thought it was a driver issue.
My DBMS is set to PotgreSQL 9.x
Air_Quality_Urban_Observatory_M (
in the preview window!
Any other thoughts/suggestions welcome please?

Have you checked the settings in your XDB file?
Go to Database\Edit Current DBMS... and expand the Scripts\Objects\Table node and scroll down to the Maxlen property (second from the bottom). It's set to 31 in my PD 16.1 PostgreSQL 8 XDB, which matches the length of your truncated value, so I suspect that's it. (Though of course I would recommend backing up your original XDB file prior to making any changes)

Thanks Laura,

That did the trick. I've now set it to 63 characters.