Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    2

    Smile Unanswered: Help required in preparing a DB2 cursor query

    Hi,
    this is my first post.
    I need to execute the below query using cursor:

    --------------------------------------------------------------------------
    SELECT DISTINCT
    VEH.A
    , B
    , C
    , D
    , E
    , F
    , FTN.G
    , FTN.H
    , FTN.I
    FROM TABLE-A VNL, TABLE-B VEH, TABLE-C SPN
    , TABLE-D SPM
    , YGT.TABLE-E FTN
    WHERE VZVEH_HAS_FK1 = VZVEH_TKN
    AND B IN ('001','002','003','004','019','020') /* Here the number of acc-type will be passed from input file which varies from 1 to 60. in this example 6 acc-type is passed. */

    AND NOT EXISTS(SELECT 1 FROM TABLE-F
    WHERE VZFTN_LINKFR_PK31 = FTN.CRTN_TS
    AND VZFTN_TYPE_PK32 = FTN.VZFTY_TYPE_PK2
    AND VZTLT_CLSF_PK2 = 'OP') /* here VZTLT_CLSF_PK2 value will be passed from file of 2 characters.*/

    AND NOT EXISTS(SELECT 1 FROM TABLE-F
    WHERE VZFTN_LINKTO_PK11 = FTN.CRTN_TS
    AND VZFTN_TYPE_PK12 = FTN.VZFTY_TYPE_PK2
    AND VZTLT_CLSF_PK2 = 'OP') /* here VZTLT_CLSF_PK2 value will be passed from file of 2 characters.*/

    AND NOT ACCOUNT_BALANCE = 0
    AND VZNSC_FOR_FKE = 'IE' /* here VZNSC_FOR_FKE value will be passed from file of 2 characters.*/

    ORDER BY VEH.A, C , B

    --------------------------------------------------------------------------

    in above query there are 60 different acount-type out of which any number of values can be passed by user from an input file.
    exm: ('001','002','003','004','019','020',''060')
    VZTLT_CLSF_PK2 value can be passed dynamically from file or jcl.
    VZNSC_FOR_FKE value can be passed dynamically from file or jcl.

    user data :
    FL1 FL2 FL3
    ----- -- --
    001 OP IE
    002
    006
    019
    020
    060

    please suggest how to execute the above query after reading input data from file. If i go for temp table to replace above input data in query, can the temp tables be filled with by reading input file and get the data inserted into temp tables before executing the above query? (DB2V10 Z/OS)

    Thanks in advance,

    amar

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Yes you can use a temporary table (or a declared global temporary table) in a query.
    You must populate that temporary table before running the query (via whatever loading/importing facilities are available on DB2 for Z/OS).
    You need to learn about parameter-markers and host-variables (depending on your programming language used to contain the query).
    There are many sample programs supplied by IBM with the DB2 installation.
    You should study those.

  3. #3
    Join Date
    May 2012
    Posts
    2
    Quote Originally Posted by db2mor View Post
    Yes you can use a temporary table (or a declared global temporary table) in a query.
    You must populate that temporary table before running the query (via whatever loading/importing facilities are available on DB2 for Z/OS).
    You need to learn about parameter-markers and host-variables (depending on your programming language used to contain the query).
    There are many sample programs supplied by IBM with the DB2 installation.
    You should study those.
    thank you db2mor for your response..
    i have decided the followings:

    step1- declare one temp-table ( for acount-type of having char(3))
    Declare two host-variable for VZTLT_CLSF_PK2 of char(2) and for VZNSC_FOR_FKE of char(2)

    step2- declare cursor as follows

    --------------------------------------------
    DECLARE CURSOR1 CURSOR FOR
    SELECT DISTINCT
    VEH.A
    , B
    , C
    , D
    , E
    , F
    , FTN.G
    , FTN.H
    , FTN.I
    FROM TABLE-A VNL, TABLE-B VEH, TABLE-C SPN
    , TABLE-D SPM
    , YGT.TABLE-E FTN
    WHERE VZVEH_HAS_FK1 = VZVEH_TKN
    AND B IN (select * from temp-table1) /*temp-table*/
    AND NOT EXISTS(SELECT 1 FROM TABLE-F
    WHERE VZFTN_LINKFR_PK31 = FTN.CRTN_TS
    AND VZFTN_TYPE_PK32 = FTN.VZFTY_TYPE_PK2
    AND VZTLT_CLSF_PK2 = :WS-OP) /* host variable*/

    AND NOT EXISTS(SELECT 1 FROM TABLE-F
    WHERE VZFTN_LINKTO_PK11 = FTN.CRTN_TS
    AND VZFTN_TYPE_PK12 = FTN.VZFTY_TYPE_PK2
    AND VZTLT_CLSF_PK2 = :WS-OP) /* host variable*/

    AND NOT ACCOUNT_BALANCE = 0
    AND VZNSC_FOR_FKE = :WS-IE /* host variable*/

    ORDER BY VEH.A, C , B
    ---------------------------------------------

    step3- read input file and insert into temp-table. Same way populate the two host variables as well by reading from sysin.

    step4- perform normal cursr operation (open/fetch/close)


    is that fine? Please reply.

    thanks you..
    amar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •