MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Utility to access remote database from Mainframes

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Wed Apr 11, 2018 3:32 pm    Post subject: Utility to access remote database from Mainframes Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Wed Apr 11, 2018 4:44 pm    Post subject: Re: Utility to access remote database from Mainframes Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Wed Apr 11, 2018 9:45 pm    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu Apr 12, 2018 2:15 pm    Post subject: Reply with quote

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
Code:

PARMS('SQL,30000')

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Thu Apr 12, 2018 2:49 pm    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu Apr 12, 2018 3:20 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Thu Apr 12, 2018 3:31 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group