Hi All
I use DS 6.0 on AIX with DB2 as DB.I am using ODBC stages for all my jobs.For every load I need to clear the staging tables,so my update logic is "Clear the table and Insert rows".Since I process more than 6 million records I get into trouble with the logfiles and the job gets aborted.I am in no position to increase the log files as the data gets varied for every load and i cannot guess the magic figure for my logfiles.So I decided to drop and recreate the staging tables for every load.But the problem is that DS doesn't accept fully qualified names in the DDL and i need to specify the schemaname for the tables.Can any one help me out with this problem?Can I run script for doing this thru before job routine?
Any help will be greatly appreciated.
Shiv
Dropping and Recreating Table
Moderators: chulett, rschirm, roy
Shiv
We have done tests in Oracle and truncate is not always faster especially with that many rows.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
We have done tests in Oracle and truncate is not always faster especially with that many rows.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Is it not possible to set a default schema (and owner, if apposite) name as an environment variable? If so, this would get around your problem.
Otherwise, if you add the schema name to the table name field in the stage type, then surely the generated DDL would include the schema name. Normally, when you import the table definition, it is qualified with the schema (and owner) name, and all the column derivations continue to use these.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Otherwise, if you add the schema name to the table name field in the stage type, then surely the generated DDL would include the schema name. Normally, when you import the table definition, it is qualified with the schema (and owner) name, and all the column derivations continue to use these.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Hi,
I had the same problem with SQL Server.
My solution was to simply use user defined sql to generate the tables.
you could try doing it in 2 ways:
1. transformer issues a single row with 1 dummy column as key going into an ODBC stage with user defined query.
2. try to do the same as source stage going to somewhere or /dev/null (with append !!!!!!!)
I hope this helps,
Roy R.
I had the same problem with SQL Server.
My solution was to simply use user defined sql to generate the tables.
you could try doing it in 2 ways:
1. transformer issues a single row with 1 dummy column as key going into an ODBC stage with user defined query.
2. try to do the same as source stage going to somewhere or /dev/null (with append !!!!!!!)
I hope this helps,
Roy R.
quote:Originally posted by kduke
We have done tests in Oracle and truncate is not always faster especially with that many rows.
Kim - I realize you said 'not always', but I'd be *really* curious what your tests were all about.
In my experience, unless a table is very large, *extremely* fragmented (FET$ and UET$ considerations play here, too) and possibly chock full o' teeny tiny extents - then 'truncate' should beat the pants off of a 'delete' any day of the week and twice on Sunday. [:0] Add to that the fact that, even if 'slower', it doesn't use any rollback...
Just curious. [;)]
-craig
We have done tests in Oracle and truncate is not always faster especially with that many rows.
Kim - I realize you said 'not always', but I'd be *really* curious what your tests were all about.
In my experience, unless a table is very large, *extremely* fragmented (FET$ and UET$ considerations play here, too) and possibly chock full o' teeny tiny extents - then 'truncate' should beat the pants off of a 'delete' any day of the week and twice on Sunday. [:0] Add to that the fact that, even if 'slower', it doesn't use any rollback...
Just curious. [;)]
-craig
Craig
You are right but 6 million rows should fall into that category. It is all based on indexes and constraints and how the database has to free up the space. The same is true on hash files. If you have a minimum modulos then clearing it takes longer. The performance gain comes on the inserts. I think in the long run I would do the truncate. It is much easier to control. You will not gain enough to make it worth while. You do need to eliminate the rollback or you do have a problem.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
You are right but 6 million rows should fall into that category. It is all based on indexes and constraints and how the database has to free up the space. The same is true on hash files. If you have a minimum modulos then clearing it takes longer. The performance gain comes on the inserts. I think in the long run I would do the truncate. It is much easier to control. You will not gain enough to make it worth while. You do need to eliminate the rollback or you do have a problem.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Hi,
I gave you an option!
(if I'm not mistaken DB2 may not have truncate option people)
use user defined sql in a job preceeding your load which drops and creates the tables.
read the 2 ways I posted earlier for implementation.
I used it in SQL Server and see no reason for it not to work for you.
in an ODBC stage I used user defined query to create a fully qualifed table i.e. CREATE TABLE dbo.#PARAM#_WEEKLY (....);
so you might need 2 jobs 1 for dropping the table and another for creating them.
Another option is to use the DS ODBC Library and open a connection to your DB2 and issue the sql statements you need ( using the include statement for ODBC.H in a batch job, search in earlier posts here ).
this way you can work-around the fact that generated DDL doesn't support fully qualified names ( I did it and it works ).
Good Luck,
Roy R.
I gave you an option!
(if I'm not mistaken DB2 may not have truncate option people)
use user defined sql in a job preceeding your load which drops and creates the tables.
read the 2 ways I posted earlier for implementation.
I used it in SQL Server and see no reason for it not to work for you.
in an ODBC stage I used user defined query to create a fully qualifed table i.e. CREATE TABLE dbo.#PARAM#_WEEKLY (....);
so you might need 2 jobs 1 for dropping the table and another for creating them.
Another option is to use the DS ODBC Library and open a connection to your DB2 and issue the sql statements you need ( using the include statement for ODBC.H in a batch job, search in earlier posts here ).
this way you can work-around the fact that generated DDL doesn't support fully qualified names ( I did it and it works ).
Good Luck,
Roy R.