Is it possible to do sql passthru to a Remote Server? I am not able to figure it out and cant really find any documentation on it. We have remote servers configured but dont know how to pass sql. Can someone please help me or point me in the right direction.



Thanks,
prodrigu

This message has been edited. Last edited by: <Kathryn Henning>,

Yes you can.

You need to use the REMOTE commands.

e.g.

REMOTE DEST ASUK2
-RUN
-REMOTE BEGIN
.
.
.
Code including SQL
.
.
.

-REMOTE END

If you need to pass back info, use PCHOLD

quote:

Originally posted by Waz:
If you need to pass back info, use PCHOLD

Or HOLD it locally (on the remote server) and on the local server create a synonym to it.

Unfortunately the CREATE SYNONYM command isn't documented, but there are some posts in these forums outlining how to use it.

Waz,

Below is the what I am trying to do but I am getting an error message. Can you please help?

REMOTE DEST ASTSTSUB
-RUN

-REMOTE BEGIN

SELECT DISTINCT
IMLITM,
IMDSC1
FROM F4101
WHERE SUBSTRING(IMGLPT,1,1) = 'F'
ORDER BY 1;

-REMOTE END

TABLE FILE SQLOUT
PRINT *
END
-EXIT  

Error Message:

  
 -INCLUDE sqlpass_remote_server_jde.fex
 -* File sqlpass_remote_server_jde.fex
 REMOTE DEST ASTSTSUB
 -RUN
 (FOC32071) REMOTE SET REQUIRES EQUAL SIGN
 -REMOTE BEGIN
 SELECT DISTINCT
 IMLITM,
 IMDSC1
 FROM F4101
 WHERE SUBSTRING(IMGLPT,1,1) = 'F'
 ORDER BY 1;
 -REMOTE END
 (FOC1517) UNRECOGNIZED COMMAND SELECT DISTINCT
 (FOC1517) UNRECOGNIZED COMMAND IMLITM,
 (FOC1517) UNRECOGNIZED COMMAND IMDSC1
 (FOC1517) UNRECOGNIZED COMMAND FROM F4101
 (FOC1517) UNRECOGNIZED COMMAND WHERE SUBSTRING(IMGLPT,1,1) = 'F'
 (FOC1517) UNRECOGNIZED COMMAND ORDER BY 1;
 (FOC1400) SQLCODE IS 1517 (HEX: 000005ED)
 : UNRECOGNIZED COMMAND SELECT DISTINCT
 L    (FOC1405) SQL PREPARE ERROR.
 TABLE FILE SQLOUT
 PRINT *
 END
 -EXIT
 0 ERROR AT OR NEAR LINE     17  IN PROCEDURE sqlpass_remote_ser
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
 BYPASSING TO END OF COMMAND

I figured out the first error statement and changed "REMOTE DEST ASTSTSUB" to "REMOTE DEST=ASTSTSUB" and that error went away.

Thank you all for your help but I was able to figure it out. Below is my code that allows me to pass sql to a remote server.

REMOTE DEST=ASTSTSUB
-RUN

-REMOTE BEGIN

SQL DB2

SELECT 
F4101.IMLITM,
F4101.IMDSC1
FROM F4101
WHERE SUBSTRING(F4101.IMGLPT,1,1) = 'F'
ORDER BY 1;

TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD
END

-REMOTE END

-**************************************************************
-*The following 2 lines depict where the file and its
-*Master will be placed in your APP dirs on your HUB server
-*Any Valid APP dir will work
-**************************************************************

APP HOLD MRODRIGTST

-**************************************************************

TABLE FILE SQLOUT
PRINT *
-*Below the XXXXX is the name you want to give the data file on the HUB
-*The YYYYY is the format you would like to save it in
ON TABLE HOLD AS DATATEST FORMAT FOCUS
END
-EXIT