Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    4

    Unanswered: Explain Plan with bind variables?

    Hi,

    well, i have all of my statements sitting in a snapshot table, so i generate a text file with all this statements (ie. dyn_sql.txt).

    later, from db2 console (under linux ) i execute this:

    set current explain mode explain
    db2 –tf dyn_sql.txt

    and my problem is that obtain this error message for each statement that contains "bind variables"

    "SQL0313N The number of host variables in the EXECUTE or OPEN statement is not equal to the number of values required. SQLSTATE=07004"


    How can i resolve this error message?
    I'm working with DB2 v8.2.2, under Linux Debian

    thx in advance

    p/d: sorry for my english

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Lucasgpl View Post
    from db2 console (under linux ) i execute this:

    set current explain mode explain
    db2 –tf dyn_sql.txt
    That doesn't look right. If you run these in CLP (is that what you mean by "db2 console"?) the second command will not run at all.

    I suggest you copy-paste here the entire session output.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2010
    Posts
    4
    The content of dyn_sql.txt:

    INSERT INTO PRAGMATEMP.RELACION_ACCION_TMP (DENOMINACION_CENTRAL,DENOMINACION_RELACION,ESPECI ALIDAD_CENTRAL, ESTADO_RELACION,ID_RELACION,NRO_RELACION_CENTRAL,S ELECCIONADO,TIPO_RELACION,USUARIO) SELECT MRC.DENOMINACION, MR.DENOMINACION, MRC.ESPECIALIDAD_ID, MR.ESTADO_RELACION, MR.ID, MRC.ID, 0 , MR.TIPO_RELACION, CAST(? AS VARCHAR(255)) FROM MINIMART.RELACION MR LEFT JOIN MINIMART.RELACION MRC ON MR.CENTRAL_ID = MRC.ID WHERE MR.ESPECIALIDAD_ID = ? AND MR.FILIAL_ID = ?;

    Later, i execute this command line:

    db2inst1@arrosdb202:~> db2 -tf dyn_sql.txt

    and the output is this:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0313N The number of host variables in the EXECUTE or OPEN statement is not
    equal to the number of values required. SQLSTATE=07004

  4. #4
    Join Date
    Jan 2010
    Posts
    4

    i find the solution!

    I put this 2 lines into the dyn_sql.txt

    set current schema = mySchema
    set current explain mode explain
    ...
    ...
    all my sql statements...
    ...
    ...


    And WORKS! all the explan plan date was saved en the EXPLAIN TABLES.. so now i can analize which index are not used

Posting Permissions

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