Importing table metadata without the catalog name

sbass1 · Post by **sbass1** » Sun Feb 15, 2009 9:20 pm

Say I import SQL Server table metadata using an ODBC definition. Later, I load column definitions for a link via the Load... button, then select the appropriate table metadata.

The generated SQL is something like:

SELECT
APPNAME.dbo.Scott.SK,
APPNAME.dbo.Scott.NK1,
APPNAME.dbo.Scott.NK2
FROM Scott
WHERE ();

where the SQL colums are generated from the metadata "Derivation" column, which was generated when I clicked the Load... button and loaded from the saved metadata.

(I get different default generated SQL based on whether it is a primary link or a lookup link).

This SQL will fail when the job is migrated across environments. Our catalogue names are something like APPNAME_DEV, APPNAME_UAT, APPNAME_PRD, etc.

So, we have to delete the catalogue component of the column derivation whenever we load columns from the saved metadata. Or, we could delete the column in the saved metadata, but it would regress whenever we reimport the metadata when the table schema changes.

So, is there a way to suppress the inclusion of the catalogue name in the column definition? Ideally, we'd just like the derivation column to remain blank, such that the generated SQL is something like:

SELECT SK, NK1, NK2 FROM schema.Scott;

Thanks,
Scott

ray.wurlod · Post by **ray.wurlod** » Sun Feb 15, 2009 10:04 pm

Through ODBC I don't believe so. You can, however, edit the imported table definition to remove them.

chulett · Post by **chulett** » Mon Feb 16, 2009 7:21 am

Have you tried dragging the table metadata from the Repository browser to the link from your database stage?

sbass1 · Post by **sbass1** » Mon Feb 16, 2009 10:25 am

Hi Craig,

I tried dragging the repository data onto the link but, unfortunately, this exhibits the same characteristics as the Load... button.

I've tried hacking up the imported metadata:

* Open each imported table, change Datasource name from Catalog_DEV to Catalog. This creates a new category (folder) under Tables --> ODBC. Cosmetic really, but will make promoting the project thru UAT and PRD "look" better. Although I don't think we promote the table metadata out of DEV anyway (not my area).

* Change the Table definition from catalog.schema.table to schema.table. This improves the generated code, removing the non-portable catalog name from the generated code. But this is a royal PITA to do for every imported table, and will regress if I ever reimport the metadata due to schema changes. Why can't this just be an option on table metadata import?

* Load column definitions for the link (either via the Load... button or drag and drop), then delete the derivation data for each column. This gives me the code I really want to generate. But, since I can't mark this entire column and delete all the values at once, this is an even bigger PITA to do, and I have to do this each time for every link in every job.

I know we're only on 7.x, but if this is the same in 8.x then I consider this a design bug. It may work as designed, it's just the design is flawed. I should be able to generate the code *I* want to generate without jumping thru so many hoops.

chulett · Post by **chulett** » Mon Feb 16, 2009 11:16 am

Too bad, that works for me, but then I'm using the DRS stage set to 'MSSQL Server'. Works the same for Oracle via OCI, so unsure why ODBC behaviour would be different.

kduke · Post by **kduke** » Mon Feb 16, 2009 6:40 pm

If you export one table defintion in XML format then it looks like this:

Code: Select all

   <TableDefinitions> 
      <Record Identifier="ODSR3\Src_CTF\CLM_GRP_SECT" DateModified="2008-03-19" TimeModified="12.01.13" Type="MetaTable" Readonly="0"> 
         <Property Name="Description" PreFormatted="1">Imported from: ODSD004 / CLM_GRP_SECT (as seen from dsdevwtc) - 03/19/2008 12:01:13 a3 
Type: VIEW 
Owner: CTF 
Fully-qualified name: "CTF"."CLM_GRP_SECT" 
Unqualified name: CLM_GRP_SECT</Property> 
         <Property Name="Version">8</Property> 
         <Property Name="QuoteChar">"</Property> 
         <Collection Name="Columns" Type="MetaColumn"> 
            <SubRecord> 
               <Property Name="Name">ACTN_CD                                                                                             </Property> 
               <Property Name="Description" PreFormatted="1"><none> Searchable? N\(20)</Property> 
               <Property Name="SqlType">12</Property> 
               <Property Name="Precision">2000</Property> 
               <Property Name="Scale">0</Property> 
               <Property Name="Nullable">1</Property> 
               <Property Name="KeyPosition">0</Property> 
               <Property Name="DisplaySize">20</Property> 
               <Property Name="PadChar"/> 
               <Property Name="APTFieldProp">null_field=&apos;&apos;</Property> 
            </SubRecord> 
            <SubRecord> 
               <Property Name="Name">CLM_NBR                                                                                             </Property> 
               <Property Name="Description" PreFormatted="1"><none> Searchable? N\(20)</Property> 
               <Property Name="SqlType">12</Property> 
               <Property Name="Precision">2000</Property> 
               <Property Name="Scale">0</Property> 
               <Property Name="Nullable">1</Property> 
               <Property Name="KeyPosition">0</Property> 
               <Property Name="DisplaySize">20</Property> 
               <Property Name="PadChar"/> 
               <Property Name="APTFieldProp">null_field=&apos;&apos;</Property> 
            </SubRecord> 
            <SubRecord> 
               <Property Name="Name">SRC_SYS_NATR_KEY                                                                                    </Property> 
               <Property Name="Description" PreFormatted="1"><none> Searchable? N\(20)</Property> 
               <Property Name="SqlType">12</Property> 
               <Property Name="Precision">2000</Property> 
               <Property Name="Scale">0</Property> 
               <Property Name="Nullable">1</Property> 
               <Property Name="KeyPosition">0</Property> 
               <Property Name="DisplaySize">20</Property> 
               <Property Name="PadChar"/> 
               <Property Name="APTFieldProp">null_field=&apos;&apos;</Property> 
            </SubRecord> 
            <SubRecord> 
               <Property Name="Name">GRP_NBR                                                                                             </Property> 
               <Property Name="Description" PreFormatted="1"><none> Searchable? N\(20)</Property> 
               <Property Name="SqlType">12</Property> 
               <Property Name="Precision">2000</Property> 
               <Property Name="Scale">0</Property> 
               <Property Name="Nullable">1</Property> 
               <Property Name="KeyPosition">0</Property> 
               <Property Name="DisplaySize">20</Property> 
               <Property Name="PadChar"/> 
               <Property Name="APTFieldProp">null_field=&apos;&apos;</Property> 
            </SubRecord> 
            <SubRecord> 
               <Property Name="Name">SECT_NBR                                                                                            </Property> 
               <Property Name="Description" PreFormatted="1"><none> Searchable? N\(20)</Property> 
               <Property Name="SqlType">12</Property> 
               <Property Name="Precision">2000</Property> 
               <Property Name="Scale">0</Property> 
               <Property Name="Nullable">1</Property> 
               <Property Name="KeyPosition">0</Property> 
               <Property Name="DisplaySize">20</Property> 
               <Property Name="PadChar"/> 
               <Property Name="APTFieldProp">null_field=&apos;&apos;</Property> 
            </SubRecord> 
            <SubRecord> 
               <Property Name="Name">CLM_RCYL_IND                                                                                        </Property> 
               <Property Name="Description" PreFormatted="1"><none> Searchable? N\(20)</Property> 
               <Property Name="SqlType">12</Property> 
               <Property Name="Precision">2000</Property> 
               <Property Name="Scale">0</Property> 
               <Property Name="Nullable">1</Property> 
               <Property Name="KeyPosition">0</Property> 
               <Property Name="DisplaySize">20</Property> 
               <Property Name="PadChar"/> 
               <Property Name="APTFieldProp">null_field=&apos;&apos;</Property> 
            </SubRecord> 
         </Collection> 
         <Property Name="SEQ-Delimiter">,</Property> 
         <Property Name="SEQ-QuoteChar">"</Property> 
         <Property Name="Locator">TableType="ODBC 3.0"|Computer="ODSD004"|SoftwareProduct="db2"|DataStore="ODSD004"|DataSchema(Creator)=""|DataCollection(Table)="CLM_GRP_SECT"</Property> 
         <Property Name="PadChar"/> 
         <Property Name="APTRecordProp">record_delim=&apos;\n&apos;, delim=&apos;,&apos;, final_delim=end, quote=double</Property> 
      </Record> 
   </TableDefinitions>

This is minus the header information. It would be easy to write SQL which could generate these rows. I generated this in DataStage BASIC. I have another which works in Visual BASIC. If you open a SQL statement in one of these languages then you can ask the language to tell you the column name and type and maybe length. Then is easy to write these rows. Next import into DataStage.

We had a project which we were writing jobs before the tables were created. The table definitions were stored in tables. So I wrote the VB version then decided the DataStage version was easier to use. It generated all tables then emails it to me. So I run one job and I import the email attachement and I am done.

sbass1 · Post by **sbass1** » Mon Feb 16, 2009 9:08 pm

Thanks for the tip Craig. Much appreciated. I've compared the two database object types, and will look to change our database objects from ODBC to DRS (this is all new development so is easy to change). It looks like the DRS object may offer some slight performance improvements as well.