Re: help with SQL in CL

[Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>]

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 
> ______________________________________________________________________
> --