Dynamic table loading
Moderators: chulett, rschirm, roy
Dynamic table loading
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
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
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?
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 50
- Joined: Wed Jul 14, 2004 7:56 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
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
Last edited by WoMaWil on Mon Jun 13, 2005 8:25 am, edited 2 times in total.
Wolfgang - great approach for Server! That's like Christmas, I will give that a try later on. {rubbing hands together in anticipation}
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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
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
Dynamic table loading
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: