Back to Tylogix Home Page

Remote SQL Technique on iSeries
Using Interactive SQL from HostSystem to access database on RemoteSystem

by Thibault Dambrine

Using Interactive SQL from HostSystem to access database on RemoteSystem - Assuming you have UserID and Password on RemoteSystem setup (authorization)

(1)   Add Relational Database Note: for “Type” my system using *SNA, some place uing *IP just have to experiment whatever works for you

                      Add RDB Directory Entry (ADDRDBDIRE)                     
Type choices, press Enter. 

Relational database . . . . . . > REMOTEDB 
Remote location: 
Name or address . . . . . . . > RMTSYS03 

Type . . . . . . . . . . . . . *SNA *SNA, *IP 
Text . . . . . . . . . . . . . . *BLANK 

Device: 
APPC device description . . . *LOC Name, *LOC 
Local location . . . . . . . . . *LOC Name, *LOC, *NETATR 
Remote network identifier . . . *LOC Name, *LOC, *NETATR, *NONE 
Mode . . . . . . . . . . . . . . *NETATR Name, *NETATR 
Transaction program . . . . . . *DRDA Character value, *DRDA 

                                                                Bottom 
F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display  
 F24=More keys                                                                  

(2)   Start Interactive SQL:  strsql

(3)   Type in “connect” then press F4

                         Specify CONNECT Statement 

Type choices, press Enter. 

Connection . . . . . . . . . . . 1 1=Remote 
2=Local (RESET) 
3=Status 



F3=Exit F5=Refresh F12=Cancel F21=Display statement                   

(4)   (4) After take option 1=Remote then press Enter

                         Specify CONNECT Statement 

Type choices, press Enter. 

Connection . . . . . . . . . . . 1 1=Remote 
2=Local (RESET) 
3=Status 

TO relational database . . . . . REMOTEDB Name, F4 for list 

USER . . . . . . . . . . . . . . User ID 

USING . . . . . . . . . . . . . Password 


F3=Exit F4=Prompt F5=Refresh F12=Cancel F21=Display statement          

(5)   Connect successfully

                             Enter SQL Statements 

Type SQL statement, press Enter. 
> CONNECT TO REMOTEDB 
Current connection is to relational database REMOTEDB. 
===> 


Bottom 
F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line 
F12=Cancel F13=Services F24=More keys 
                     

(6)   Experimented with some SELECT statement and seem to be working fine then DISCONNECT

                             Enter SQL Statements 

Type SQL statement, press Enter. 
> SELECT * FROM MyLibrary/myItemFile WHERE ItemNum = '001115' 
SELECT statement run complete. 
> disconnect current 
DISCONNECT completed. 
===> 




Bottom 
F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line 
F12=Cancel F13=Services F24=More keys                         

(7)   You can use Work with Relational Database by key in WRKRDBDIRE then press enter

                  Work with Relational Database Directory Entries 

Position to . . . . . . 

Type options, press Enter. 
1=Add 2=Change 4=Remove 5=Display details 6=Print details 

Relational Remote 
Option Database Location Text 

AS400SRC *LOCAL 
REMOTEDB RMTSYS03 


Bottom 
F3=Exit F5=Refresh F6=Print list F12=Cancel 
                                                                                                   

Dan's recommendation is to disconnect with RCLDDMCNV on command line again.

Very important – in order to make it work, you must know what is the Local Database name on remote system. In my case Local Database name is "REMOTEDB" and is on RMTSYS03. When you are on RMTSYS03 and using command WRKRDBDIRE to see it. You will see something like REMOTEDB *LOCAL .If you don’t see or have Local Database then you can add it by using ADDRDBDIRE and parameter for "Remote Location: name and address . . . ." must be *LOCAL

When ADDRDBDIRE, no need for "Remote Network Identifier. . ." let it default to *LOC

Back to Tylogix Home Page