Manually edit table definitions

[adama]

Hi,

I am pulling records from a number of files on an AS400, they have exactly the same columns but sit in different libraries.

I am passing a parameter to the job to select from the correct library (i.e. #File#DAFIL and in my column derivation for the table I have #File#.FileName.ColumnName.

This works well, but my problem now is that the files have over 150 columns and I have to go and edit each column derivation to put the above information in. It is a pain in the butt.

I imported the definitions from the AS400 over ODBC.

Is it possible to edit the file that sits somewhere in DS that holds this information so I can do a find and replace? Or is there a better way to do this?

Thanks

[rasi]

Unfortunately we don't have find and replace function in dastage. I had posted this in the Product enhancement forum.

I don't think you have any other way to do it apart from manually editing it.

Thanks
Rasi

[ray.wurlod]

The problem is twofold. First, it's not a file, it's a database table (called DS_METADATA) that holds table definitions. Second, the metadata for DS_METADATA are deliberately not completely defined, so that you do not have column names via which to effect an UPDATE statement.

IF you knew the record layout for DS_METADATA (which is not in the public domain and is, technically, Ascential's intellectual property), and IF you knew how to manipulate hashed file file dictionaries, then you could build such a utility. It may, however, void your warranty if you do so; the licence agreement specifically prohibits reverse-engineering.

[dhiraj]

i think the following should work. but make sure you have back up copies of your job before attempting it.

1)export the job as a xml file
2) write a pgm to traverse through the xml file and replace the value of "derivation" property of collection "columns" for the required stage to whatever you want .
3)reimport the job.
4) and for the definition in the DS repository, you could replace it with the new one you've just created.

You need to research the structure of the xml file, which datastage generates. Make sure you don't break any thing else.

We've done this before for an aggregator stage, when we wanted to apply the same aggregate funtion to all the columns. Unfortunately i've lost that code and the developer who created the utility is not anymore with us. However we've never done this with source stages. Share the results if you try this

Dhiraj

[trobinson]

I'm sure I am misunderstanding but I'm curious, having never worked with AS400, as to why you even need the column derivations. If the file layouts are identical for ALL files, then it would seem to me all you need is a way to point to the correct library as you have already done. The derivations in the input stage can either be eliminated all together or the file library qualification dropped to generalize them to work with any library as long as the layouts themselves are identical. Is this something unique to AS400 access?

[chulett]

That's what I've been wondering as well, but knowing absolutely nothing about the specifics of AS400-ness was unwilling to take point on that particular trail. Thanks, t!

[trobinson]

I hear that. One must triple check everything when replying to a reply from the Big Ray-huna.

[chulett]

[ray.wurlod]

There is a field in a table definition record (table definition records are in the DS_METADATA table) containing "CMetaColumn/n" where n is the number of column definitions in the table definition. This introduces the collection of column definitions.

Let us assume that this is found in field number X. Ascential reserve the right to change the value of X. By inspection the following can be ascertained. Each field is multi-valued.
Field Contents
X+1 Column names
X+2 Column descriptions
X+3 unused
X+4 SQL type (enumerated, see DSD_BCI.H)
X+5 Precision
X+6 Scale
X+7 Nullable (0 = NOT NULL)
X+8 Is Key Column (1 = is key column)
X+9 Display width (= width in fixed-width format Seq Files)

Shortly following this is a second collection of column definitions, also headed "CMetaColumn/n". This collection is used for the column definitions associated with an imported stored procedure.

Later in the record there are two collections "CMetaColumnExt/n". These contain the extended properties required if the column definition was sourced from, or belongs to, a mainframe data source. I have not yet determine fully what the various fields within these collections are.

Because the location of the collection identifiers may change, I prefer code of the following ilk:

Code: Select all

FindStr "CMetaColumn" In TableDefinition Setting FMC, VMC, SMC 
Then 
   ColumnCount = Field(TableDefinition<FMC>, "/", 2, 1) 
   Ans = ColumnCount 
   ColumnNames = TableDefinition<FMC+1> 
   ColumnDescs = TableDefinition<FMC+2> 
   ColumnTypes = TableDefinition<FMC+4>    ; * SQL type (enumerated) 
   ColumnPrecs = TableDefinition<FMC+5>    ; * precision 
   ColumnScale = TableDefinition<FMC+6> 
   ColumnNulls = TableDefinition<FMC+7>    ; * 0 = not null 
   ColumnIsKey = TableDefinition<FMC+8>    ; * 1 = column is key column 
   ColumnWidth = TableDefinition<FMC+9> 
   SQLTypeNames = "" 

   Loop 
       Remove ColumnTypeCode From ColumnTypes Setting More 
       SQLTypeNames<1,-1> = (If ColumnTypeCode = 0 Then "Unknown" Else Field(SQL.TYPE.NAMES, ",", ColumnTypeCode, 1)) 
   While More 
   Repeat 

   Msg = "" 
   For LineCount = 1 To ColumnCount 
      Msg<-1> = ColumnNames<1,LineCount> : TAB : SQLTypeNames<1,LineCount> : TAB : ColumnPrecs<1,LineCount> : TAB : ColumnScale<1,LineCount> : TAB : (If ColumnNulls<1,LineCount> = 0 Then "NOT NULL" Else "") : TAB : (If ColumnIsKey<1,LineCount> = 1 Then "Key" Else "") 
   Next LineCount 
   Call DSLogInfo(Msg, "Columns") 

End 

The variable TableDefinition is a record that has been read from DS_METADATA.