Remote Database setup

benny.lbs · Post by **benny.lbs** » Tue Mar 08, 2005 4:08 am

I have difficulity in configuring DataStage to connect to remote DB2.

In DataStage server,

1. I have created a database node and alias that map to remote database under the DB2 instance db2inst1, and the connection is ok.

2. run db2grant.sh, but the following message return.

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0554N An authorization ID cannot grant a privilege to itself.
SQLSTATE=42502
DB20000I The SQL command completed successfully.
DB20000I The TERMINATE command completed successfully.

3. run db2setup.sh, many message return. some have line number, but some havn't.

LINE MESSAGES FOR db2uexpm.bnd
------ --------------------------------------------------------------------
SQL0038W The bind option SQLERROR CONTINUE has been
activated since it is required when binding this DB2-supplied
list file to DB2/MVS, SQL/DS, or OS/400.
2508 SQL0804N The application program parameters for the current
request are not valid. Reason code "103". If a host
variable or SQLVAR in the SQLDA is invalid then: host
variable/SQLVAR number = "1", SQLTYPE = "964", SQLLEN = "4",
host variable/SQLVAR type = "INPUT". SQLSTATE=07002
2556 SQL0804N The application program parameters for the current
request are not valid. Reason code "103". If a host
variable or SQLVAR in the SQLDA is invalid then: host
variable/SQLVAR number = "1", SQLTYPE = "964", SQLLEN = "4",
host variable/SQLVAR type = "INPUT". SQLSTATE=07002
2652 SQL0804N The application program parameters for the current
request are not valid. Reason code "103". If a host
variable or SQLVAR in the SQLDA is invalid then: host
variable/SQLVAR number = "1", SQLTYPE = "960", SQLLEN = "4",
host variable/SQLVAR type = "INPUT". SQLSTATE=07002
2699 SQL0804N The application program parameters for the current
request are not valid. Reason code "103". If a host
variable or SQLVAR in the SQLDA is invalid then: host
variable/SQLVAR number = "1", SQLTYPE = "960", SQLLEN = "4",
host variable/SQLVAR type = "INPUT". SQLSTATE=07002
2757 SQL0804N The application program parameters for the current
request are not valid. Reason code "103". If a host
variable or SQLVAR in the SQLDA is invalid then: host
variable/SQLVAR number = "1", SQLTYPE = "968", SQLLEN = "4",
host variable/SQLVAR type = "INPUT". SQLSTATE=07002
2806 SQL0804N The application program parameters for the current
request are not valid. Reason code "103". If a host
variable or SQLVAR in the SQLDA is invalid then: host
variable/SQLVAR number = "1", SQLTYPE = "968", SQLLEN = "4",
host variable/SQLVAR type = "INPUT". SQLSTATE=07002

4. Use DB2 API plug-in stage to test server connectivity. It works well. It can load, unload and delete normally.

5. Use DB2 UDB Enterprise stage, and setup as follow.
a. Client Instance Name = db2inst1
b. Database
c. Password
d. Server = db2inst1
e. User
f. Table

6. When trying to view data, the following message occur.

>##E TNDB 000312 18:04:55(000) <DB2_UDB_Enterprise_0,0> Error Idx = 1;
>DB2 Driver Embedded SQL: -30082;
>sqlcode = 08001;
>sqlstate = SQL30082N Attempt to establish connection failed with security reason "17"
>("UNSUPPORTED FUNCTION"). SQLSTATE=08001
>
>##E TNDB 000050 18:04:55(001) <DB2_UDB_Enterprise_0,0> Failed to connect to the database
>##E TNDB 000312 18:04:55(002) <DB2_UDB_Enterprise_0,0> Error Idx = 5;
>DB2 Driver Embedded SQL: -1024;
>sqlcode = 08003;
>sqlstate = SQL1024N A database connection does not exist. SQLSTATE=08003

I have missed some steps? or any other setting error?

Would you please provide me some lights. Thanks a lot.

Thank you for reading.

Eric · Post by **Eric** » Tue Mar 08, 2005 6:17 am

Hope this helps a little ... DB2 Error codes ...

SQL0804N The application program parameters for the current
request are not valid. Reason code "103".

Code: Select all

SQL0804N The application program input parameters for the current request are not valid. Reason code "<reason-code>". 

103 
The length specified in SQLVAR.SQLLEN or SQLVAR2.SQLLONGLEN is incorrect for the SQL type given in SQLVAR.SQLTYPE

I think you need to get the setup.sh script running first, then run the grant.sh script, and then try to make a connection.

T42 · Post by **T42** » Tue Mar 08, 2005 10:40 am

Contact Ascential for the latest copy of the DB2 Connectivity document. I believe it is called "DataStage EE Remote DB2 Configuration". There might also be a copy on the Ascential's forum download section.

The guideline within the manual is incorrect. Hopefully you will find the document useful in pinpointing the cause of your problem.

benny.lbs · Post by **benny.lbs** » Tue Mar 08, 2005 7:22 pm

I found some information from "DataStageEE_Remote_DB2_Configuration.doc".

The DB2/UDB Enterprise stage requires DB2 Enterprise Server Edition on UNIX with Data Partitioning Facility (DPF) option. (Before DB2 v8, this was also called "DB2 EEE".) Furthermore, the DB2 hardware/UNIX/software platform must match the hardware/software platform of the DataStage ETL server.

These plug-in stages are designed for lower-volume access to DB2 databases without the DPF option installed (prior to v8, "DB2 EE"). These stages also provide connectivity to non-UNIX DB2 databases, or databases on UNIX platforms that differ from the platform of the DataStage ETL server.

Now my DB2 server is v7 on WINNT platform, but my DataStage ETL server is v8 on UNIX platform.

So that is the reason why I can use plug-in stage to connect, but failed with DB2/UDB Enterprise stage ?

Besides, anyone can show me the sample content of db2nodes.cfg ?

Thanks a lot!

T42 wrote:Contact Ascential for the latest copy of the DB2 Connectivity document. I believe it is called "DataStage EE Remote DB2 Configuration". There might also be a copy on the Ascential's forum download section.

The guideline within the manual is incorrect. Hopefully you will find the document useful in pinpointing the cause of your problem.