Connecting from DB2 to another DB2 on a different platform

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

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.                                            

and which error did you get ? the 113 or the 188?

I got SQL0113 first. Then I added the dire and now I get SQL0188

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???
 

do you have any application you are using to perform the connection such as db2 connect?

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

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

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.

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

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

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

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

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???

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

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.