Dynamic table loading

jusami25 · Post by **jusami25** » Mon Jun 13, 2005 1:13 am

Hi all,

I would like to create a single DS job for loading a table by passing the table name. If I would have a job parameter for the table name, I would like to use that table name to retrieve the table structure (if I can access the data dictionary for retrieving the columns and data type) Then I would like to load the information.

If that possible in the server edition? and how about the parallel edition??

If anyone have any idea about how to speed up the loading of tables (even if they are quite similar) instead of creating tons of jobs, please let me know. This will help a lot.

Thanks so much in advance
Julio

ArndW · Post by **ArndW** » Mon Jun 13, 2005 1:27 am

Jusami25,

Unfortunately this is not possible in Server, and in Px it is possible to use implicit columns but perhaps not to the extent that you wish.

Since an ETL process usually processes columns in addition to reading/writing them this type of job design is not too common. Are you reading and writing the data without any modifications at all?

krystlecsy · Post by **krystlecsy** » Mon Jun 13, 2005 1:57 am

Yeah, I am encountering a similar situation. I was hoping to pass in the column names through some routine? Is that possible then? Has anyone written such routines before? In other words, we just want to achieve some dynamic table loading.

WoMaWil · Post by **WoMaWil** » Mon Jun 13, 2005 2:15 am

I've never tested it, but in theory it should work, and here have been some folks who told they succeed. If you work with user defined SQL and you retrieve all fields like this:

PARAM1=TABLE_A
PARAM2=FIELD1||','||Field2||','||Field3
PARAM3=TABLE_B
PARAM4=FIELDA,FIELDB,FIELDC

the reading User-SQL would be so:

SELECT #PARAM2# FROM #PARAM1#;

the writing User-SQL would be so:

INSERT INTO #PARAM3# Fields (#PARAM4#) VALUES (:1)

That's it for numberfields.

For text-fields don't forget to quote them with single quotation-mark '
For Date and Time-fields use the to_char and to_date function in Oracle or simular in other databases.

If somebody succeeds to proceed in this way, please let me know.

The PARAM2 and the PARAM4 can be contructed easily by using the Metadata.

Kind regards
Wolfgang

ArndW · Post by **ArndW** » Mon Jun 13, 2005 2:36 am

Wolfgang - great approach for Server! That's like Christmas, I will give that a try later on. {rubbing hands together in anticipation}

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 13, 2005 3:36 am

The idea is very good.

You may also need to ensure dates are converted correctly in source and target.

elavenil · Post by **elavenil** » Mon Jun 13, 2005 7:04 am

Hi Wolfgang,

It is great idea. I tried and it is working fine.

Created four parameters as mentioned and i have used 2 columns (one integer and one varchar ). Did not find any problem while selecting the data from the source table as you mentioned but while loading the data into Oracle, it is not accepting only one value in the VALUES section. So i just split the columns into two in the transformer and assigned the columns in the value section. Then it works.

Once again thanks to have great idea like this.

Regards
Saravanan

jusami25 · Post by **jusami25** » Fri Jun 24, 2005 7:06 am

Very good idea indeed,

but I'm thinking about the next stage after the OCI or DB stage. How if the columns names or datatypes are changing, then we have to use a table definition. How will we create dynamic table definitions also ??

Thanks the idea was veri good
Julio

ray.wurlod · Post by **ray.wurlod** » Fri Jun 24, 2005 12:22 pm

You create dynamic table definitions the same way you would in the database; that is, it's not possible without a lot of hacking.