Dynamic SQL issue - IBM AS400

This is a discussion on Dynamic SQL issue - IBM AS400 ; I wonder if anyone can help me. I am trying to use an Execute Immediate - SQL statement to search for the existence of a record in a file. The program gets the key to search for and attempts to ...

+ Reply to Thread
Results 1 to 8 of 8

Thread: Dynamic SQL issue

  1. Dynamic SQL issue

    I wonder if anyone can help me. I am trying to use an Execute
    Immediate - SQL statement to search for the existence of a record in a
    file. The program gets the key to search for and attempts to return
    a count by the Select (*count) into :Counter, which will be non zero
    if the record is there.

    I am using this rather than the SETLL method to establish the
    existence as the file contains a BLOB field.

    As the SQL is dynamic, it does not return a value to the variable and
    my joblog shows that the variable is not able to be used in a dynamic
    SQL statement.

    Do I have to declare a cursor and fetch the record to establish its
    existence or am I being thick ?

    Thanks (in anticipation) for any help.

    Regards

    Nick

  2. Re: Dynamic SQL issue

    ballingern@harlosh.com wrote:
    > I wonder if anyone can help me. I am trying to use an Execute
    > Immediate - SQL statement to search for the existence of a record in a
    > file. The program gets the key to search for and attempts to return
    > a count by the Select (*count) into :Counter, which will be non zero
    > if the record is there.
    >
    > I am using this rather than the SETLL method to establish the
    > existence as the file contains a BLOB field.
    >
    > As the SQL is dynamic, it does not return a value to the variable and
    > my joblog shows that the variable is not able to be used in a dynamic
    > SQL statement.
    >
    > Do I have to declare a cursor and fetch the record to establish its
    > existence or am I being thick ?
    >


    If you are using embedded SQL and wish to set the count into a host
    variable :Counter, I believe you could us SELECT INTO (no need to
    declare a cursor):
    http://publib.boulder.ibm.com/infoce...ode=int_174189

    exec sql select count(*) into :Counter
    from where ...

    --
    Karl Hanson

  3. Re: Dynamic SQL issue

    Karl Hanson wrote:
    > ballingern@harlosh.com wrote:
    >> I wonder if anyone can help me. I am trying to use an Execute
    >> Immediate - SQL statement to search for the existence of a record in a
    >> file. The program gets the key to search for and attempts to return
    >> a count by the Select (*count) into :Counter, which will be non zero
    >> if the record is there.
    >>
    >> I am using this rather than the SETLL method to establish the
    >> existence as the file contains a BLOB field.
    >>
    >> As the SQL is dynamic, it does not return a value to the variable and
    >> my joblog shows that the variable is not able to be used in a dynamic
    >> SQL statement.
    >>
    >> Do I have to declare a cursor and fetch the record to establish its
    >> existence or am I being thick ?
    >>

    >
    > If you are using embedded SQL and wish to set the count into a host
    > variable :Counter, I believe you could us SELECT INTO (no need to
    > declare a cursor):
    > http://publib.boulder.ibm.com/infoce...ode=int_174189
    >
    >
    > exec sql select count(*) into :Counter
    > from
  4. where ...

    You're right about not needing to declare a cusor. However, selecting
    the count when all you want to know is if there is at least one row that
    meets the selection criteria is inefficient. If there are many rows
    that meet the criteria, getting the count can be really slow.

    Instead, you can select a constant into the variable based on an
    existence test. Using V5R3 syntax for clarity:

    /free
    counter = *zero;
    /end-free

    c/exec sql
    c+ select 1
    c+ into :counter
    c+ from sysibm/sysdummy1
    c+ where exists
    c+ (select *
    c+ from

    c+ where )
    c/end-exec

    /free
    if counter = 1;
    [do stuff]


  5. Re: Dynamic SQL issue

    C Clear Counter;
    c/exec sql
    c+ select 1
    c+ into :counter
    c+ from

  6. c+ where
    C+ Fetch First row only
    c/end-exec

    Should be faster
    If 1 is returned at least 1 row is found.

    Birgitta

  7. Re: Dynamic SQL issue

    Hauser@sss-software.de wrote:
    > C Clear Counter;
    > c/exec sql
    > c+ select 1
    > c+ into :counter
    > c+ from

  8. > c+ where
    > C+ Fetch First row only
    > c/end-exec
    >
    > Should be faster
    > If 1 is returned at least 1 row is found.
    >
    > Birgitta


    I ran the following statements using Run and Explain in the iSeries
    Navigator SQL script window:

    select '1'
    from libname.inp95
    where cono95 = '40'
    fetch first row only;

    select '1'
    from sysibm.sysdummy1
    where exists
    (select * from libname.inp95
    where cono95 = '40');

    There are tens of millions of rows for company 40.

    The second statement was consistently faster as far as the optimization
    time: 2211 milliseconds for the first statement vs. 521 milliseconds
    for the second. I ran it a few times each way and reversed the order of
    execution a few times. The statement open times were generally
    comparable: 45K-50K microseconds either way. The statement fetch time
    showed as "NotAvailable" for both.

    What's interesting is that the second statement requires a nested loop
    join of the two files that the first obviously does not, but the
    optimization was faster anyway. The optimizer used the same index over
    INP95 in both cases.

  9. Re: Dynamic SQL issue

    By which query engine were the statements performed?


    Birgitta

  10. Re: Dynamic SQL issue

    Hauser@sss-software.de wrote:
    > By which query engine were the statements performed?
    >
    >
    > Birgitta


    SQE

  11. Re: Dynamic SQL issue

    On 7 Oct, 04:24, Rudy Canoza wrote:
    > Hau...@sss-software.de wrote:
    > > By which query engine were the statements performed?

    >
    > > Birgitta

    >
    > SQE


    Thanks for the responses everyone. They are most helpful. I'm most
    grateful.

    Nick