Re: Embedded SQL to a DDM file. -- MIDRANGE-L

With all due respect:

This works just fine...and I can assure you that ddmfiles.detailq is a
DDM file with the PF on another system

select * from ddmfiles.detailq
SELECT statement run complete.

NOW, the problem could be on how you created your DDM file... I used:

CRTDDMF FILE(XXX/XXX) RMTFILE(LIB/FILE) RMTLOCNAME(*RDB) RDB(S1xxxxx)

On 2021-03-11 15:49, Rob Berendt wrote:

Basically you cannot do sql on a ddm file.
It's such a simple test
CRTDDMF QTEMP/MYLIB to yourself.
CRTDDMF FILE(QTEMP/MYFILE)
RMTFILE(SYSIBM/SYSDUMMY1)
RMTLOCNAME('127.0.0.1' *IP)
DSPPFM FILE(QTEMP/MYFILE)
STRSQL
select * from qtemp.myfile
SQL7011
Message . . . . : MYFILE in QTEMP not table, view, or physical file.

It is not a comm issue, especially when going to 127.0.0.1.

However, there is an exception to the rule. Not that you can get this ddm exception to point to a remote system though.
dltf qtemp/myfile
Object MYFILE in QTEMP type *FILE deleted.
STRSQL

CREATE ALIAS QTEMP.MYFILE FOR SYSIBM.SYSDUMMY1

Alias MYFILE created in QTEMP.

SELECT * FROM QTEMP.MYFILE

SELECT statement run complete.
DSPFD QTEMP/MYFILE
Type of file . . . . . . . . . . . . . . . : Device
Device type . . . . . . . . . . . . . . . . : DDM
Auxiliary storage pool ID . . . . . . . . . : 00001
DDM File Attributes
File level identifier . . . . . . . . . . . : 1210311154658
Creation date . . . . . . . . . . . . . . . : 03/11/21
Remote file . . . . . . . . . . . . . . . . : RMTFILE 'SYSIBM/SYSDUMMY1

Remote location RMTLOCNAME
Name or address . . . . . . . . . . . . . : '*LOCAL'
Type . . . . . . . . . . . . . . . . . . : *SNA

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of dr2@xxxxxxxx
Sent: Thursday, March 11, 2021 3:35 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Embedded SQL to a DDM file.

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

First make sure you can DSPPFM the DDM file on the target box.... If
you can't then you have a comm issue.

On 2021-03-11 11:57, Rob Berendt wrote:

I cannot get sql to work against any DDM file. When I use it straight from STRSQL I get:

CRTDDMF FILE(QTEMP/MYFILE) RMTFILE(SYSIBM/SYSDUMMY1) RMTLOCNAME(gdihq *IP)
STRSQL
select * from qtemp.myfile
SQL7011
MYFILE in QTEMP not table, view, or physical file.

Stop using DDM for SQL.

Instead:
Let's say you have FROMSYSTEM and TOSYSTEM.
On FROMSYSTEM:
ADDRDBDIRE RDB(TOSYSTEM) RMTLOCNAME(tosystem *IP)
ADDRDBDIRE RDB(FROMSYSTEM) RMTLOCNAME(*LOCAL)
ADDSVRAUTE USRPRF(ME) SERVER(TOSYSTEM) USRID(ME) PASSWORD(...) replacing ME with your userid
On TOSYSTEM
ADDRDBDIRE RDB(FROMSYSTEM) RMTLOCNAME(fromsystem *IP)
ADDRDBDIRE RDB(TOSYSTEM) RMTLOCNAME(*LOCAL)
ADDSVRAUTE USRPRF(ME) SERVER(FROMSYSTEM) USRID(ME) PASSWORD(...) replacing ME with your userid

Then on FROMSYSTEM it becomes easy to do:
STRSQL
SELECT * FROM TOSYSTEM.SYSIBM.SYSDUMMY1

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Tom Deskevich
Sent: Thursday, March 11, 2021 9:47 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Embedded SQL to a DDM file.

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

I have an RPG program with embedded SQL that accesses the data via a DDM file. From the research I did, I found that you should specify *RDB on the DDM RMTLOCNAME parameter.
Then put the RDB value from the RDB directory entry in the RDB parameter. The RDB directory entry references the IP of the remote box.
My understanding is that the SQL should qualify the file with *LIBL by default. Yet I get an error in the job log stating that it cannot find the file.
The name of my DDM library is different from the name of the library on the remote box.
My boss tells me that they have never got SQL to work with DDMS. They had to put stored procedures on the remote box.

Embedded SQL: dt is an external data structure that points to a file in qtemp with the same fields as XXXPY70P.
begsr GetData ;

RowsFetched = 0 ;
clear dt ;
cs=case;

exec sql DECLARE C0 CURSOR FOR
SELECT * FROM XXXPY70P
WHERE DECCY=:CY AND DECYR=:YR AND DECNO=:CS
ORDER BY DESNO,DESSNO
OFFSET :Offset ROWS;

exec sql OPEN C0 ;

exec sql FETCH C0 FOR :NbrOfRows ROWS INTO :Dt ;

exec sql GET DIAGNOSTICS :RowsFetched = ROW_COUNT ;

exec sql CLOSE C0 ;
endsr ;

Harriscomputer

Tom Deskevich
Programmer/Analyst

P: 814-472-6066 x201134
F: 814-472-5019
E: TDeskevich@xxxxxxxxxxxxxxxxxx

[cid:HLG_logo_14bd0fcf-3182-4b52-9120-a2e250d74b09.jpg]

172 Gateway Road PO Box 568
Ebensburg, Pennsylvania
15931
www.harrislocalgov.com [1] [1 [1]]<http://www.harrislocalgov.com>

This message has been sent on behalf of a company that is part of the Harris Operating Group of Constellation Software Inc.

If you prefer not to be contacted by Harris Operating Group please notify us<http://subscribe.harriscomputer.com/>.

This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged or confidential or otherwise legally exempt from disclosure. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

Links:
------
[1] http://www.harrislocalgov.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

Links:
------
[1] http://www.harrislocalgov.com