SQL View on 1 AS/400 using data from another AS/400

135 pts.
AS/400 SQL View
Data Description Specifications
We have 2 AS400 machines connected with a 1g pipe. I need to create a SQL view on one of the machines using data that is in DDS physical files on the other machine. How can I accomplish this?

Answer Wiki

Thanks. We'll let you know when a new response is added.

we have 3 as400’s, a production machine, programmers machine, and operations machine.. you can use CRTDDMF on the ‘to’ machine to create a gateway from one machine to the other. we use it for rpg & clp applications, I don’t see why it wouldn’t work for sql also.


DDM file . . . . . . . . . . . . file
Library . . . . . . . . . . . qtemp
Remote file:
File . . . . . . . . . . . . . dctran
Library . . . . . . . . . . ftbdc

Remote location:
Name or address . . . . . . . almmis

Type . . . . . . . . . . . . . *IP

on the ‘to’ box for a one time ddm, create like above in qtemp with some general name. then also on the ‘to’ box do the cpyf as below:


From file . . . . . . . . . . . file
Library . . . . . . . . . . . qtemp
To file . . . . . . . . . . . . dctran
Library . . . . . . . . . . . ftbdc
From member . . . . . . . . . . *FIRST
To member or label . . . . . . . *FIRST
Replace or add records . . . . . *add
Create file . . . . . . . . . . *yes
Print format . . . . . . . . . . *CHAR

you can also set a permanent DDM….just don’t put in qtemp and give it a real name (not file).

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Oldtonew
    Thank you very much for the quick response to my question. I hadn't thought about trying DDM. However, if I try to run a simple interactive SQL select on the DDM file I get the message "CUS1DDM in mylibr not table, view, or physical file.". I get the same message when I try to create a view. This view will actually be over 22 different files on the production machine. I don't have a lot of DASD on the second machine and don't really want to duplicate the data if I don't have to.
    135 pointsBadges:
  • Pcatlin
    Unfortunately, DDM won't work for SQL (as you've discovered) What you can do however, is to remotely attach to the 2nd iSeries within SQL and then execute SQL statements against the remote database. This can be done both interactively and within a SQLxxx program. The steps are as follows: 1) The remote database must be defined to the local iSeries... Use command WRKRDBDIRE to do this. 2) You must issue a "CONNECT" sql statement for the remote database. To do this you will need a vaild user ID and password on the remote system 3) Issue the SQL statements you need. When doing this, I've not found a convenient way of referencing BOTH systems. You can CONNECT again to your local system, but that seems to break the link to the remote system. Connection has an overhead associated with it so flipping back and forth doesn't seem to be good practice. What I've done is to code database access to the local system in traditional RPG "CHAIN, READ, WRITE, UPDATE" commands, and access the remote database using SQL... It's a kludge but it works... Phil C
    200 pointsBadges:
  • Oldtonew
    Phil - Thank you for the respponse about using the *CONNECT statement. I have used this before and it works great if you want to return a data set or manipulate data on the second machine. I am trying to create a view on one machine using the data from the second machine. I haven't figured out a way to this this without copying the data files from one machine to the other, build the view, do what I need to with the data and then remove the copies. I was hoping that there was an easier, faster way.
    135 pointsBadges:
  • Pcatlin
    If I understand your need correctly, you want information on system 1 to be joined with information on system 2 something like: select a.field1, b.field2, a.field3 from system1.library1.file1 as a join system2.library2.file2 as b on a.field1 = b.field1 I couldn't get something like this to work... I got a SQL0114 message: Relational database SYSTEM2 not the same as current server. Cause... Relational database SYSTEM2 was specified in a 3 part name in the statement. However, either the name is not the same as the current server *N, or the name is not the same as a relational database name specified previously in the statement. This seems to indicate that if you had 2 different databases on the same System i (using ASPs) then you could connect between the 2 databases, but it can't be used to connect between databases on 2 different systems. Sorry! Phil C BTW... I find it somewhat ironic that if you create an alias using SQL, then go back to green screen and DSPOBJD that the file type is DDM, but that you can't use DDM files for their other capabilities.
    200 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: