MVSFORUMS.com :: View topic - Utility to access remote database from Mainframes

Utility to access remote database from Mainframes

through

#1: Utility to access remote database from Mainframes Author: Magesh_J,

Posted: Wed Apr 11, 2018 3:32 pm
—
Hi,

I am trying to run a query to fetch records from IBM UDB DB2 to mainframes.

I tried using DSNTIAUL.

Challenges -
Single row fetch
Cannot perform SQL statement.

I know this can be done thru Cobol, but is there any utility exists to access remote database with a query.

Regards,
Magesh

#2: Re: Utility to access remote database from Mainframes Author: kolusu, Location: San Jose

Posted: Wed Apr 11, 2018 4:44 pm
—

Magesh_J wrote:

Hi,

I am trying to run a query to fetch records from IBM UDB DB2 to mainframes.

I am assuming that your UDB is on window/Linux on the PC side and you are trying to access the data that is in UDB from the mainframe using a JCL? Is that true? Do you have the product DB2 connect?

As long as you have PLAN defined for DSNTIAUL for UDB it should be the same as accessing the data from z/OS DB2 database.

You mentioned that you can access the UDB data using COBOL program, how is the plan defined?

#3: Author: Magesh_J,

Posted: Wed Apr 11, 2018 9:45 pm
—
Hi Kolusu,

Thanks for looking into this.

kolusu wrote:

I am assuming that your UDB is on window/Linux on the PC side and you are trying to access the data that is in UDB from the mainframe using a JCL?

Yes.

kolusu wrote:

Do you have the product DB2 connect?

Yes, But that was used to connect PC to mainframe table not on the other way. So we should use DB2 connect to access UDB ?

Quote:

As long as you have PLAN defined for DSNTIAUL for UDB it should be the same as accessing the data from z/OS DB2 database.

I could fetch data from DSNTIAUL, but i cannot use SQL option

Please refer the Link shows the limitations of DSNTIAUL

PARMS('SQL'), If i use, it is failing with -804 with no error message.

Code:

DSNT490I SAMPLE DATA UNLOAD PROGRAM
DSNT505I DSNTIAUL OPTIONS USED: SQL
DSNT408I SQLCODE = -804, ERROR:
DSNT418I SQLSTATE = 07002 SQLSTATE RETURN CODE
DSNT415I SQLERRP = SQLRA13E SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -2145779603 0 0 0 -1450 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'801A006D' X'00000000' X'00000000' X'00000000' X'FFFFFA56' X'00000000' SQL DIAGNOSTIC
INFORMATION

Also I am forced to use PARMS('1') which is limiting the utility not to perform multi fetch.

bind statements

Code:

DSN SYSTEM(DB2X)
BIND PACKAGE (UDBTEST.DSNTIAUL) +
MEMBER(DSNTIAUL) +
LIBRARY('DB2.DB2X.DBRMLIB.DATA') +
QUALIFIER (DB2IMPL) +
SQLERROR(CONTINUE) +
VALIDATE(BIND) +
ISOLATION(CS) +
ACTION(REPLACE)

SYSTSIN

Code:

DSN S(DB2X)
RUN PROGRAM (DSNTIAUL) PLAN (DSNTIAUL) PARMS('1') -
LIB('DB2.DB2X.RUNLIB.LOAD')
END

Thanks
Magesh

#4: Author: kolusu, Location: San Jose

Posted: Thu Apr 12, 2018 2:15 pm
—

Magesh_J wrote:

Yes, But that was used to connect PC to mainframe table not on the other way. So we should use DB2 connect to access UDB ?

You can use it both ways

Magesh_J wrote:

Please refer the Link shows the limitations of DSNTIAUL

PARMS('SQL'), If i use, it is failing with -804 with no error message.

Code:

Also I am forced to use PARMS('1') which is limiting the utility not to perform multi fetch.

That is a 8 year old reference document. What version of DB2 UDB are you using?

try passing the parm as

Code:

PARMS('SQL,30000')

#5: Author: Magesh_J,

Posted: Thu Apr 12, 2018 2:49 pm
—

kolusu wrote:

That is a 8 year old reference document. What version of DB2 UDB are you using?

UDB DB2 9.7 FP11

PARMS('SQL,30000')

Code:

********************************* TOP OF DATA **********************************
DSNT490I SAMPLE DATA UNLOAD PROGRAM
DSNT505I DSNTIAUL OPTIONS USED: SQL,30000
DSNT408I SQLCODE = -804, ERROR:
DSNT418I SQLSTATE = 07002 SQLSTATE RETURN CODE
DSNT415I SQLERRP = SQLRA13E SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -2145779603 0 0 0 -1450 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'801A006D' X'00000000' X'00000000' X'00000000' X'FFFF
INFORMATION

Thanks
Magesh

#6: Author: kolusu, Location: San Jose

Posted: Thu Apr 12, 2018 3:20 pm
—
Magesh_J,

What does your sysin contain ? just the table name or do you have a semicolon in there?

#7: Author: Magesh_J,

Posted: Thu Apr 12, 2018 3:31 pm
—
SELECT * FROM Qualifer.TABLENAME;

Tried with/without Semicolon, result is same.

Below code is working in DSNTAUIL

Code:

//SYSIN DD *
DBNAME.QUALIFER.TABLENAME
/*
//SYSTSIN DD *
DSN S(DB2X)
RUN PROGRAM (DSNTIAUL) PLAN (DSNTIAUL) PARMS('1') -
LIB('DB2.DB2X.RUNLIB.LOAD')
END

Thanks
Magesh

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1