| View previous topic :: View next topic |
| Author |
Message |
Magesh_J
Intermediate
Joined: 21 Jun 2014
Posts: 259
Topics: 54
|
 Posted: Wed Apr 11, 2018 3:32 pm Post subject: Utility to access remote database from Mainframes |
 |
|
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 |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12366
Topics: 75
Location: San Jose
|
| Posted: Wed Apr 11, 2018 4:44 pm Post subject: Re: Utility to access remote database from Mainframes |
|
|
| 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?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Magesh_J
Intermediate
Joined: 21 Jun 2014
Posts: 259
Topics: 54
|
| Posted: Wed Apr 11, 2018 9:45 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12366
Topics: 75
Location: San Jose
|
| Posted: Thu Apr 12, 2018 2:15 pm Post subject: |
|
|
| 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: |
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.
|
That is a 8 year old reference document. What version of DB2 UDB are you using?
try passing the parm as
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Magesh_J
Intermediate
Joined: 21 Jun 2014
Posts: 259
Topics: 54
|
| Posted: Thu Apr 12, 2018 2:49 pm Post subject: |
|
|
| 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 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12366
Topics: 75
Location: San Jose
|
| Posted: Thu Apr 12, 2018 3:20 pm Post subject: |
|
|
Magesh_J,
What does your sysin contain ? just the table name or do you have a semicolon in there?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Magesh_J
Intermediate
Joined: 21 Jun 2014
Posts: 259
Topics: 54
|
| Posted: Thu Apr 12, 2018 3:31 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
