OR

You can use the db2 command in QSH: Something like this:
/*******************************************************************/
/* Don't present a Qshell terminal session, keep a log of all */
/* activity. */
/*******************************************************************/

ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) +
VALUE('FILEAPPEND=/tmp/sy150uc.shell.log')
MONMSG CPFA980 /* Error adding value */
ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) +
REPLACE(*YES) +
VALUE(Y)
MONMSG CPFA980 /* Error adding value */

/********************************************************************/
/* Build and run the command */
/********************************************************************/
CHGVAR &CMDINSRT +
VALUE('whatever SQL statement you want')

CHGVAR &Command ('db2 "' *cat &cmdinsrt *tcat '"')

QSH cmd(&Command)

This uses a bit more code in the CLP but does not require quite a few more objects to be in existence in order to run the command.

Both work, both work well. You pick.

This is all in the list archives as well if you look for it, it's where I found it.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 3/26/2012 3:04 PM, rob@xxxxxxxxx wrote:

http://archive.midrange.com/midrange-l/200207/msg00949.html
http://archive.midrange.com/midrange-l/200312/msg00675.html
http://wiki.midrange.com/index.php/SQL



Rob Berendt
-- Group Dekko Dept 1600 Mail to: 2505 Dekko Drive Garrett, IN 46738 Ship to: Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com From: "Stone, Joel" <Joel.Stone@xxxxxxxxxx> To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>, Date: 03/26/2012 03:52 PM Subject: help with SQL in CL Sent by: midrange-l-bounces@xxxxxxxxxxxx I built a RUNSQL command from a magazine article. Intermittently it will fail because one space is dropped between parm &v8 and &v9. Any suggestions how I can solve this? Is there a better way to do this so there is one parm len 1100 instead of 20 parms length 55? Thanks! ===> RUNSQL STMT('select AAAAA_SHIPMENT_U_ID_NBR, AAAAA_BILL_OF_LADING_DATE,AAA AA_SUPRSHIP_ID_NBR, AAAAA_LOCA TION_FORMAT_CODe , AAAAA_ORIG IN_CLIENT_NBR , AAAAA_SHIP _BILLED_DEST_COde,AAAAA_COMMODITY_CODE,AAAAA_SHIPMENT_TYPE_CODE,AAAAA_SCALE_TICK ET_NBR,AAAAA_APP_ATTMPTD_NBR,AAAAA_SHIPMENT_TYPE_CODE from SHIPFILE') /|\ | | This space disappears and ------------- Causes SQL statement to fail!! Type SQL Statement ************************** Beginning of Data ********************************* 0001.00 &V1&V2&V3&V4&V5&V6&V7&V8&V9&V10&V11&V12&V13&V14&V15&V16&V17&V18&V19&V20 ***************************** End of Data ************************************ RUNSQL STMT('select AAAAA_SHIPMENT_U_ID_NBR, AAAAA_BILL_OF_LADING_DATE,A0 176_SUPRSHIP_ID_NBR, A0 176_LOCATION_FORMAT_CODe , AAAAA_ORIGIN_CLIENT_NBR , AAAAA_SHIP_BILLED_DEST_COde,AAAAA_COMMODITY_CODE,AAAAA_SHIPME NT_TYPE_CODE,AAAAA_SCALE_TICKET_NBR,AAAAA_APP_ATTMPTD_NBR,AAAAA_SHIPMENT_ TYPE_CODE from shipment') 2600 - STRQMQRY QMQRY(RUNSQL2) SETVAR((V1 'select AAAAA_SHIPMENT_U_ID_NBR, AAAAA_BILL_OF_LADING_DA') (V2 'TE,AAAAA_SUPRSHIP_ID_NBR,') (V3 ' AAAAA_LOCATION_FORMAT_CODe ,') (V4 ' AAAAA_ORI') (V5 'GIN_CLIENT_NBR ,') (V6 ' AAAAA_SHIP_BILLED_DEST_COde,AAAAA_COMMO') (V7 'DITY_CODE,AAAAA_SHIPMENT_TYPE_CODE,AAAAA_SCALE_TICKET_N') (V8 'BR,AAAAA_APP_ATTMPTD_NBR,AAAAA_SHIPMENT_TYPE_CODE from') (V9 'shipment') (V10 ' ') (V11 ' ') (V1 ^------------------------------space is now gone, so SQL fails!!! Token <END-OF-STATEMENT> was not valid. Valid tokens: , FROM INTO. RUN QUERY command failed with SQLCODE -104. RUN QUERY command ended due to error. STRQMQRY command failed. QWM2701 received by RUNSQL at 2600. (C D I R) ? C Function check. QWM2701 unmonitored by RUNSQL at statement 2600, instruction X'00D5'. RUNSQL CL: CMD PROMPT('Run SQL Statement') PARM KWD(STMT) TYPE(*CHAR) LEN(1100) RUNSQL CL: PGM PARM(&STMT) DCL VAR(&STMT) TYPE(*CHAR) LEN(1100 DCL VAR(&V1) TYPE(*CHAR) LEN(55) DCL VAR(&V2) TYPE(*CHAR) LEN(55) DCL VAR(&V3) TYPE(*CHAR) LEN(55) DCL VAR(&V4) TYPE(*CHAR) LEN(55) DCL VAR(&V5) TYPE(*CHAR) LEN(55) DCL VAR(&V6) TYPE(*CHAR) LEN(55) DCL VAR(&V7) TYPE(*CHAR) LEN(55) DCL VAR(&V8) TYPE(*CHAR) LEN(55) DCL VAR(&V9) TYPE(*CHAR) LEN(55) DCL VAR(&V10) TYPE(*CHAR) LEN(55) DCL VAR(&V11) TYPE(*CHAR) LEN(55) DCL VAR(&V12) TYPE(*CHAR) LEN(55) DCL VAR(&V13) TYPE(*CHAR) LEN(55) DCL VAR(&V14) TYPE(*CHAR) LEN(55) DCL VAR(&V15) TYPE(*CHAR) LEN(55) DCL VAR(&V16) TYPE(*CHAR) LEN(55) DCL VAR(&V17) TYPE(*CHAR) LEN(55) DCL VAR(&V18) TYPE(*CHAR) LEN(55) DCL VAR(&V19) TYPE(*CHAR) LEN(55) DCL VAR(&V20) TYPE(*CHAR) LEN(55) CHGVAR &V1 %SST(&STMT 001 55) CHGVAR &V2 %SST(&STMT 056 55) CHGVAR &V3 %SST(&STMT 111 55) CHGVAR &V4 %SST(&STMT 166 55) CHGVAR &V5 %SST(&STMT 221 55) CHGVAR &V6 %SST(&STMT 276 55) CHGVAR &V7 %SST(&STMT 331 55) CHGVAR &V8 %SST(&STMT 386 55) CHGVAR &V9 %SST(&STMT 441 55) CHGVAR &V10 %SST(&STMT 496 55) CHGVAR &V11 %SST(&STMT 551 55) CHGVAR &V12 %SST(&STMT 606 55) CHGVAR &V13 %SST(&STMT 661 55) CHGVAR &V14 %SST(&STMT 716 55) CHGVAR &V15 %SST(&STMT 771 55) CHGVAR &V16 %SST(&STMT 826 55) CHGVAR &V17 %SST(&STMT 881 55) CHGVAR &V18 %SST(&STMT 936 55) CHGVAR &V19 %SST(&STMT 991 55) CHGVAR &V20 %SST(&STMT 1046 55) STRQMQRY QMQRY(RUNSQL2) SETVAR((V1 &V1) (V2 &V2) (V3 + &V3) (V4 &V4) (V5 &V5) (V6 &V6) (V7 &V7) + (V8 &V8) (V9 &V9) (V10 &V10) (V11 &V11) + (V12 &V12) (V13 &V13) (V14 &V14) (V15 + &V15) (V16 &V16) (V17 &V17) (V18 &V18) + (V19 &V19) (V20 &V20)) ENDPGM ______________________________________________________________________ This outbound email has been scanned for all viruses by the MessageLabs Skyscan service. For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].