Solved

Connecting from DB2 to another DB2 on a different platform

Posted on 2008-10-13
17
596 Views
Last Modified: 2013-12-26
I am attempting to connect to another DB2 database on a Unix server using SQLRPGLE (embedded SQL statements)  I am getting a SQL0113  or SQL0188  error message
EDI002-1013.doc
0
Comment
Question by:bcloring08
  • 9
  • 5
  • 3
17 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22702960
did you make sure that the destination is listed in the database directory?
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzaii/rzaiiodbc37.htm
0
 

Author Comment

by:bcloring08
ID: 22703154
Yes,  I have it setup as follows:
                    Display Relational Database Entry Detail                    
                                                                               
 Relational database  . . . . . . :   EDSQATDB                                  
   Relational database alias  . . :   EDSQATDB                                  
 Remote location:                                                              
   Remote location  . . . . . . . :   10.4.70.51                                
     Type . . . . . . . . . . . . :   *IP                                      
   Port number or service name  . :   50000                                    
   Remote authentication method:                                                
     Preferred method . . . . . . :   *ENCRYPTED      (should this be *USRIDPWD or something else maybe???)
Other choices are:                                              
  *USRID                                      
  *USRIDPWD                                  
  *ENCRYPTED                                  
  *KERBEROS                                  
  *SAME                                      

                         
     Allow lower authentication . :   *ALWLOWER                                
 Text . . . . . . . . . . . . . . :   EDW QA Database                          
                                                                               
 Relational database type . . . . :   *REMOTE                                  
                                                                               
                                                                         Bottom
 Press Enter to continue.                                                      
                                                                               
 F3=Exit   F12=Cancel                                                          
 (C) COPYRIGHT IBM CORP. 1980, 2005.                                            
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22703201
and which error did you get ? the 113 or the 188?
0
 

Author Comment

by:bcloring08
ID: 22703212
I got SQL0113 first. Then I added the dire and now I get SQL0188
0
 

Author Comment

by:bcloring08
ID: 22719866
Well, I have even tried to substring the entire CONNECT string together and then EXECUTE IMMEDIATE :STRING and now I get SQL0104.  There must be a way to connect from  the AS400 to another DB2 database on a Unix server???  Someone out there must be doing this???
 
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22719879
do you have any application you are using to perform the connection such as db2 connect?
0
 

Author Comment

by:bcloring08
ID: 22719907
No, I am trying to use native SQLRPGLE programming with embedded SQL statements.  Here is the latest attempt (source code): <see attached>
Bob Loring


EDI002.txt
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22719956
but i think you need some intermidiate software in order to access a unix server from as400
i know that i needed db2 connect to access unix from a mainframe
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:bcloring08
ID: 22720040
That is what I am hearing from a lot of forums.  I guess I may have to look into that avenue and forget about trying to connect directly through RPG program.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22724149
Bob:

The RPG doesn't give much of a clue what your connection string looks like. The syntax error can't be reviewed if we have no idea what's in variable CONNCT.

Also, you show:

> Port number or service name  . :   50000

How did you determine that 50000 was the correct port? Does that port show as being registered as a service on the remote system? If it does show, what service is it?

If it's not a DRDA service, then you won't be able to use a DRDA connection. I've never connected to DB2 on a Unix system, so I don't know what connection types are available. It would irritate me if IBM didn't make DRDA available easily -- IMO, Unix DB2 should accept remote connections from other DB2 platforms by default (as should DB2 on Windows if it's purchased as an "Enterprise" kind of version).

Other than DRDA, I would expect a JDBC (Type 4) driver both to be available and to run on your AS/400 (or whatever you have that's later).

Tom
0
 

Author Comment

by:bcloring08
ID: 22724526
Hello Tom,
    In the subroutine EstConnect I concatenate all of the fields into CONNCT
So in the end it has (and I confirmed this in debug)
CONNCT = 'CONNECT TO EDSQATDB USER EDIQAAPP USING 'the password''
Yes the port and all server criteria was given to me by EDW   DBA
0
 

Author Comment

by:bcloring08
ID: 22835260
Has anyone in AS400 land ever attempted and succeeded to connect to a UNIX DB2 system using native SQL statements??? This is what I am attempting to do.  I guess I must be pioneering this expedition?
EDI002.txt
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22835584
Bob:

Port 50000 seems correct for DB2/UDB, so what you have shown so far seems correct. DRDA _should_ work for you, as near as I can tell. However, rather than using a compiled program, I'd suggest using simple interactive SQL first.

Start SQL with the STRSQL command. When the SQL statement prompt appears, either type the CONNECT command on the SQL command line and press <F4> to get into prompting mode, or paste your expected CONNECT statement and press <Enter>.

Use interactive SQL as your test facility to ensure that all connection configuration is correct before trying to execute compiled programming. That means that routes, firewalls, DRDA databases, etc., all need to cooperate -- joblog messages or system log messages may indicate trouble spots.

Please report here if error messages don't supply useful info.

Tom
0
 

Author Comment

by:bcloring08
ID: 22835685
Hello Tom,

     Yes I tried to connect manually (after I did a CHGJOB CCSID(000037),  the connection works fine manually.  One thing I noticed is, the password does not show up on the SQL statement.  I think that is my stumbling block.  I think SQL encrypts the password where SQLRPGLE doesn't.  That may be the whole problem.  If I can figure out how to encrypt the password, maybe that will work either with CONNECT statement in program or EXECUTE IMMEDIATE  CONNCT statement???  Any ideas on this???
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22837447
Bob:

The first possibility that comes to mind is upper-/lower-case, but I'd expect that to be the first thing you thought of as well. Next, I'd look to ensure that the characters used in the password were from an invariant character set -- character translation from EBCDIC to ASCII can be tricky enough without wondering if a [#] on one system is going to be the same character on a different system. Next, I'd look to see if quotes should be included or excluded from the connection string when run in embedded SQL. Finally, I'd check if single- or double-quote marks were proper (always minding how they might affect case.)

And as before, I'm not at all familiar with DB2 on Unix; so I'd only be guessing for any of those combinations.

I would want to use a specific test user on the DB2/Unix side that had very limited permissions along with a basic password, probably all upper-case to begin. When/if that one worked, I would expand to use a second profile with a lower-case password. When/if both of those worked, I'd feel comfortable that all the basic configuration elements were reasonable.

From there, the actual user/password combination in embedded SQL should be reduced close to some limited trial and error.

Of course, always keep in mind that a PTF -- on _either_ side -- might be the real problem.

Tom
0
 

Author Comment

by:bcloring08
ID: 23094209
Does anyone know about DB2 Connect and how it would interface to either a CL program or RPGILE program to trigger the connection process to Unix database???  Everyone is telling me that SQL connect from AS400 TO Unix is next to impossible using native imebbed SAL statements in the RPGILE program, so I guess I will persue the DB2 Connect plan.
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 23094295
db2 connect allows you to connect to a remote db2 as if it was a local one
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
AS400  SNDDST Truncating 5 76
NotAlone Challenge 20 72
Configuration Assistant in DB2 10.1 3 29
DS3512 Replace HDD 2 14
Introduction: Load and Save to file, Document-View interaction inside the SDI. Continuing from the second article about sudoku.   Open the project in visual studio. From the class view select CSudokuDoc and double click to open the header …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now