While trying to use 'DB2 UDB Enterprise stage', I am getting the following error :
##I TFSC 000001 09:20:08(005) <main_program> APT configuration file: /DataStage/751A/Ascential/DataStage/Configurations/default.apt
>##E TNDB 000000 09:20:09(001) <DB2_UDB_Enterprise_0> Error Idx = 1;
>DB2 Driver Embedded SQL message: SQL0552N "DSWORLD" does not have the privilege to perform operation "BIND".
>SQLSTATE=42502
>;
>sqlcode = -552;
>sqlstate = 42502
>##E TNDB 000000 09:20:09(002) <DB2_UDB_Enterprise_0> Program not bound to database
>Attempting to bind to the database /DataStage/751A/Ascential/DataStage/PXEngine/bin/db2esql.bnd ....failed
However I am able to run the same query using a DB2 API stage.
What could be the reason.
Problem using DB2 UDB Enterprise stage
Moderators: chulett, rschirm, roy
Are you using the same id, both in DataStage and outside DataStage, to connect to the database. If yes then search in google for "SQL0552N" to get more info on the error message. Even your dba can help you with that. It might be as easy as setting a few variables.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
The DB2 database which you are accessing should have DB2ESQL package created. The job will bind this package if it is not available. Contact your DBA and as him to give your Database id BindAdd authority.
However I would advise to bind this package manually to keep control of the ownership of this package. You can bind this package as dsadm user and grant access to public. The dsadm user should have BindAdd authority. You can bind this package manually by executing following commands:
$ cd `cat /.dshome`
$ . ./dsenv
[Assuming your db2profile is being called from dsenv]
$ cd $APT_ORCHHOME/bin
$ db2setup.sh <Name of the Database>
Once you are done you will have to connect to DB2 and execute grant command:
$ db2 "GRANT EXECUTE ON package DSADM.DB2ESQL TO PUBLIC"
Please note db2setup.sh assumes that you have database node same as ETL node. If you have ETL Server and DB server on different nodes then, run this script in one of the fiollowing ways:
1. Running the script from ETL node: Modify db2setup.sh script and change CONNECT $1 to CONNECT $1 user dsadm. [You will be prompted for a password of dsadm]
2. Run the script from DB Controller node then you will not have to modify the script.
Hope it helps........
However I would advise to bind this package manually to keep control of the ownership of this package. You can bind this package as dsadm user and grant access to public. The dsadm user should have BindAdd authority. You can bind this package manually by executing following commands:
$ cd `cat /.dshome`
$ . ./dsenv
[Assuming your db2profile is being called from dsenv]
$ cd $APT_ORCHHOME/bin
$ db2setup.sh <Name of the Database>
Once you are done you will have to connect to DB2 and execute grant command:
$ db2 "GRANT EXECUTE ON package DSADM.DB2ESQL TO PUBLIC"
Please note db2setup.sh assumes that you have database node same as ETL node. If you have ETL Server and DB server on different nodes then, run this script in one of the fiollowing ways:
1. Running the script from ETL node: Modify db2setup.sh script and change CONNECT $1 to CONNECT $1 user dsadm. [You will be prompted for a password of dsadm]
2. Run the script from DB Controller node then you will not have to modify the script.
Hope it helps........
Assume everything I say or do is positive