DSXchange

Hi ALL,

How to achieve the DB2 Bulk load using DB2 Connector stage? There are many options available so what exactly we can select from those? Please share your thoughts.

The DB2 is on Z/OS system we tried with connector stage with bulk load and we gave the ftp ip address ,user id and password.But the job got aborted.

Try posting exactly what options you used as well as the exact messages you got in DataStage when the job aborted. You'll have a better chance of getting a relevant response.

Please find the below error message we are receiving:

Transfer to dataset JDBCBDW.IN00000 failed with error: 550-SVC99 RETURN CODE=4 S99INFO=0 S99ERROR=1224 HEX=04C8 S99ERSN code X'00000FD6'.
550 Unable to create data set JDBCBDW.IN00000 for STOR command.
cat: 0652-054 Cannot write to output.
There is no process to read data written to a pipe.
(CC_DB2ZLoadRecordDataSetConsumer::complete, file CC_DB2ZLoadRecordDataSetConsumer.cpp, line 3,460)


DB2_Connector_0,0: LOAD utility output: DSNU003I DSNUTILS - NOT INVOKED APF AUTHORIZED

Below are options we enabled:

We have enabled write mode :Bulk Load

Enabled Bulk load to DB2 on z/OS:YES
Load Method:MVS dataste(s)
transfer type:ftp
transfer to:given the ip address of the mainframe db2
user id
password

below options we havent given anything:
Datafileattribute
Inputdatafiles
filedisposition
Discard dataset
Error dataset
Map dataset
work1 dataset
work2 dataset

The error message (DSNUTILS - NOT INVOKED APF AUTHORIZED) indicates it is a permissions issue.

The IBM supplied stored procedure DSNUTILS is there to allow unauthorized callers to run DSNUTILB. But you need to ensure that the WLM-managed environment that DSNUTILS stored procedure runs in is has APF authorization (else DSNUTILB will abend).

Contact your z/OS admin team and ask them to provide your id with the correct authority.

I have given the ftp ip address of the DB2 database.Do we need to give the ip address of the mainframe or Db2 database.Could you please elaborate on the above comments so that i can well describe about this to admin.
Thanks for your contribution.

I think your login statement is correct, so no, you don't need to change the FTP. It is getting logged into z/OS, it just doesn't have permissions to run the command. Show the error message to your z/OS admin, they should be able to figure it out once they know what login id you are using.

i have one question... in Connector Stage for bulk load operation we have properties as Discard dataset, Error dataset, Map dataset, work1 dataset
and work2 dataset .

I don't understand the purpose of those so i have left blank. Do they create the datasets in the mainframe and then load to the database or what could be the process?

Thanks a lot

You are correct, they are used to name temporary MVS datasets used during the loads. If I remember correctly the prefix is optional, and if you don't specify it, it uses your user-id to construct temporary datasets.

I'd recommend getting a copy of the Connectivity Guide for IBM DB2 Databases off the IBM website for your appropriate release and looking at the section entitled DB2 connector properties for bulk loading to z/OS since it covers a lot of the properties you are looking at.