How to pass database name as parameter for connectivity

[harborboy76]

Is there a way to pass database name as a parameter in DataStage ?

Basically, there can be many (hundreds) of databases on a single server, and I'm trying to find a way to dynamically switch the data source / target at run time.

I don't think this can be accomplished within ODBC Connector stage because DSN is bound to a single database (and I'm trying to avoid creating DSN for every database on the server).

Any way to accomplish this with SQL Server Enterprise or MS OleDB stage ?

Thank you!

[greggknight]

SELECT COMPANY_ID, ERP_DIVISION_ID, DIVISION_ID, LOCATION_ID, FEES_CODE_ID, FEES_CODE_DESC, FEES_CALC_CODE, UNIT_FEE_AMOUNT, FEES_CALC_TYPE, FEES_COMPARE_TEST_TYPE, FEES_COMPARE_TEST_AMOUNT, FEES_TIMING_FLAG, CHARGE_REG_ORDER_FLAG, CHARGE_POINT_OF_SALE_FLAG, CHARGE_BATCH_FLAG, CHARGE_CREDITS_FLAG, CHARGE_DROP_SHIP_FLAG, USE_ITEM_EXCLUSION_FLAG, ONE_CHARGE_PER_DAY_FLAG, ITEM_CLASS_ID, DROP_SITE_INVOICE_FLAG
FROM #STAGE_LIB#.dbo.tStage_#TableName#
Where
BIZSTAT = 'N' ;

[cppwiz]

Also, when you setup the DSN name in the odbc.ini file, you can leave the Database line blank if you don't want a default database:

Code: Select all

[DSNNAME]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMmsss24.so
Description=DataDirect SQL Server Wire Protocol driver
Database=
LogonID=
Password=
Address=hostname,1433
QuotedId=No
AnsiNPW=No

Then the user name, password and database are passed into the job. We use ODBC Connectors like this and they work great.