Datastage

[sudhirmah]

We need to develop a RDBMS independent job to put data into a table, will ODBC stage serve the purose.

TIA
SAM

[ArndW]

Yes, it will work in almost all cases. There are some idiosyncracies between different vendors and their interpretation of the ODBC standard, but if you use common datatypes and straightforward inserts, updates & deletes you will be as portable as you can get.

[roy]

Hi,
How about the DRS (Dynamic RDBMS) stage?
IHTH,

[ArndW]

Roy,

I forgot about that recent addition to server (and Px). I don't know what the salient differences are between using an ODBC stage and a dynamic RDBMS stage with type "ODBC", though. Can you further my understanding there?

[kcbland]

We need to develop a RDBMS independent job to put data into a table, will ODBC stage serve the purose.

TIA
SAM

A sequential text file, probably pipe or comma delimited, is the universal format. You can write your data to a file, it then becomes an exercise in writing the appropriate connectivity job to load it.

The problem with ODBC stage is that each driver has custom information that needs to be setup for it. When you first import table definitions, you must press a get info button on the ODBC stage to talk to the driver. This returns different results depending on the driver. Failure to do this causes the job to have issues. So, you can't simply switch a job to write to Oracle when the stage was setup for SQL-Server.

The DRS has similar issues, and its one-size-fits-all solution is never going to be optimal for performance, it's only optimal for ease-in-use. But it too has issues.

To build a universal adapter approach, writing to a sequential file allows you to write a loading script appropriate to each target, and use the command line loading utility that most databases provide (isql, sqlldr, dbaccess, tmu, etc.)

[roy]

come to think of it I never got to actually try (lets say) building an Oracle insert job with DRS and then change the RDBMS type (at run time) to something else and checked what happens when I run it against the new and valid DB type.

I guess we'll need some one to make some tests and report the results.

[vmcburney]

It works. The DRS is designed to let you choose the RDBMS at run time. As long as you haven't put in any RDBMS specific code in your user-defined SQL or generated WHERE clause such as functions or database optimisation hints.

You can set the database type to a job parameter to change it at run time or if you have already hard coded it you can export and do a search and replace to switch your database type.

[sudhirmah]

thanks all, DRS works perfectly fine

[ray.wurlod]

DRS came about largely as a result of the PeopleSoft decision to use DataStage; they needed a generic mechanism for deploying canned ETL jobs to their customers who, of course, use different databases for running the PeopleSoft applications.