Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    127

    Unanswered: Dynexpln fails with PREP DYNEXPLN.sqc error

    PREP DYNEXPLN.sqc PACKAGE USING DYNEXPLN

    LINE MESSAGES FOR DYNEXPLN.sqc
    ------ --------------------------------------------------------------------
    SQL0060W The "C" precompiler is in progress.
    1 SQL4945N The use of a parameter marker is not valid.
    SQLSTATE=42610
    SQL0092N No package was created because of previous
    errors.
    SQL0091W Precompilation or binding was ended with "2"
    errors and "0" warnings.

    TERMINATE
    DB20000I The TERMINATE command completed successfully.

    I checked the documentation with no luck. When I use host variables, it doesn't work. If they are populated, it works. I know db2batch doesn't work unless they r populated.

    one of the queries I'm trying to run explain on:

    This is a dynamic SQL captured from a tool,
    insert into RF53RICK.PUBLISH_ITEMS (PUBLISH_ITEM_ID, PUBLISH_ITEM_ACTION, SRC_CA
    TALOG_ID, SRC_CATEGORY_ID, SRC_PRODUCT_ID, DST_CATALOG_ID, DST_CATEGORY_ID, CREA
    TE_DATE, MODIFY_DATE, MODIFY_USER) (SELECT NEXTVAL FOR RF53RICK.PUBLISH_ITEM_ID,
    CAST (? AS VARCHAR (4000)), CAST (? AS INT), CAST (NULL AS INT), CAST (? AS INT
    ), CAST (NULL AS INT), CAST (NULL AS INT), CURRENT TIMESTAMP, CURRENT TIMESTAMP,
    CAST (? AS VARCHAR (4000)) FROM (VALUES ('X')) DUAL WHERE NOT EXISTS (SELECT *
    FROM RF53RICK.PUBLISH_ITEMS pi WHERE pi.SRC_CATALOG_ID = ? AND pi.SRC_PRODUCT_I
    D = ?));

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Dynexpln fails with PREP DYNEXPLN.sqc error

    Dynexpln creates an embedded C program with static sql(the one you give) to generate the access plan ..... Hence, parameter markers are not allowed ...


    If you wish to use parameter markers, you can use db2exfmt tool ...

    Steps :

    1) db2 "explain plan for <statement>"

    2) db2exfmt command

    The output is slightly different from the dynexpln output, but has much more information ....

    Please let me know how you get along ...

    HTH

    Sathyaram



    Originally posted by Kota
    PREP DYNEXPLN.sqc PACKAGE USING DYNEXPLN

    LINE MESSAGES FOR DYNEXPLN.sqc
    ------ --------------------------------------------------------------------
    SQL0060W The "C" precompiler is in progress.
    1 SQL4945N The use of a parameter marker is not valid.
    SQLSTATE=42610
    SQL0092N No package was created because of previous
    errors.
    SQL0091W Precompilation or binding was ended with "2"
    errors and "0" warnings.

    TERMINATE
    DB20000I The TERMINATE command completed successfully.

    I checked the documentation with no luck. When I use host variables, it doesn't work. If they are populated, it works. I know db2batch doesn't work unless they r populated.

    one of the queries I'm trying to run explain on:

    This is a dynamic SQL captured from a tool,
    insert into RF53RICK.PUBLISH_ITEMS (PUBLISH_ITEM_ID, PUBLISH_ITEM_ACTION, SRC_CA
    TALOG_ID, SRC_CATEGORY_ID, SRC_PRODUCT_ID, DST_CATALOG_ID, DST_CATEGORY_ID, CREA
    TE_DATE, MODIFY_DATE, MODIFY_USER) (SELECT NEXTVAL FOR RF53RICK.PUBLISH_ITEM_ID,
    CAST (? AS VARCHAR (4000)), CAST (? AS INT), CAST (NULL AS INT), CAST (? AS INT
    ), CAST (NULL AS INT), CAST (NULL AS INT), CURRENT TIMESTAMP, CURRENT TIMESTAMP,
    CAST (? AS VARCHAR (4000)) FROM (VALUES ('X')) DUAL WHERE NOT EXISTS (SELECT *
    FROM RF53RICK.PUBLISH_ITEMS pi WHERE pi.SRC_CATALOG_ID = ? AND pi.SRC_PRODUCT_I
    D = ?));
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Posts
    127
    Sathyaram, Thanks. With parameter markers, using explain format worked.

Posting Permissions

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