Topic
  • 8 replies
  • Latest Post - ‏2013-01-03T16:54:59Z by mwandishi
Noh.Aonne
Noh.Aonne
29 Posts

Pinned topic cursor in load from cursor

‏2013-01-02T15:45:36Z |
Hello.

I have a question that's bugging me.
I ran "load from cursor" as in example:

C:\>db2 "DECLARE C1 CURSOR FOR SELECT * FROM FECHNER.SALES"

C:\>db2 "LOAD FROM C1 OF CURSOR MESSAGES C:\load_sales_tmp.msg INSERT INTO FECHNER.SALES_TMP NONRECOVERABLE"

I was wondering, what happens to the cursor? How does it close? Is it closed after load command?

Tnx!
Updated on 2013-01-03T16:54:59Z at 2013-01-03T16:54:59Z by mwandishi
  • mwandishi
    mwandishi
    47 Posts

    Re: cursor in load from cursor

    ‏2013-01-02T16:07:41Z  
    Hi,

    I assume that autocommit is on ( default ), if this is the case then unless 'with hold' is specified then the cursor is declared implicitly as 'WITHOUT HOLD' meaning it will be closed upon a commit.

    If the clp autocommit feature is off then it may remain open

    Best regards,
    Stephen Levett.
  • mwandishi
    mwandishi
    47 Posts

    Re: cursor in load from cursor

    ‏2013-01-02T16:34:22Z  
    • mwandishi
    • ‏2013-01-02T16:07:41Z
    Hi,

    I assume that autocommit is on ( default ), if this is the case then unless 'with hold' is specified then the cursor is declared implicitly as 'WITHOUT HOLD' meaning it will be closed upon a commit.

    If the clp autocommit feature is off then it may remain open

    Best regards,
    Stephen Levett.
    Hi,

    Here is a non-LOAD CLP example:

    Disable autocommit:

    export DB2OPTIONS='+a -c +ec'
    db2 list command options

    Command Line Processor Option Settings

    Backend process wait time (seconds) (DB2BQTIME) = 1
    No. of retries to connect to backend (DB2BQTRY) = 60
    Request queue wait time (seconds) (DB2RQTIME) = 5
    Input queue wait time (seconds) (DB2IQTIME) = 5
    Command options (DB2OPTIONS) = +a -c +ec

    Option Description Current Setting



    -a Display SQLCA OFF
    -c Auto-Commit OFF <<<===
    ......

    Without hold example:

    db2 "DECLARE c1 CURSOR FOR SELECT tabname FROM syscat.tables"
    DB20000I The SQL command completed successfully.

    db2 "OPEN c1"
    DB20000I The SQL command completed successfully.

    db2 "FETCH FROM c1"

    TABNAME

    ACT

    1 record(s) selected.

    db2 "FETCH FROM c1"

    TABNAME

    ADEFUSR

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CATALOG

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CL_SCHED

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CUSTOMER

    1 record(s) selected.

    db2 commit
    DB20000I The SQL command completed successfully.

    db2 "FETCH FROM c1"
    DB21030E The cursor "C1" has not been opened.
    --Closed due to commit and not with hold.
    With hold example:
    db2 "DECLARE c1 CURSOR with hold FOR SELECT tabname FROM syscat.tables "

    db2 "OPEN c1"

    db2 "FETCH FROM c1"

    TABNAME

    ACT

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    ADEFUSR

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CATALOG

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CL_SCHED

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CUSTOMER

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    DEADREP

    1 record(s) selected.

    db2 commit

    db2 "FETCH FROM c1"

    TABNAME

    DEPARTMENT

    1 record(s) selected.

    db2 "FETCH FROM c1"

    TABNAME

    DEPT

    1 record(s) selected.

    --We can still fetch despite the commit.

    In your LOAD scenario the autocommit will close the cursor as it wasn't defined 'with hold'. It is basically all about the CLP.

    I hope this additional info helps?

    Best regards,
    Stephen Levett.
  • Noh.Aonne
    Noh.Aonne
    29 Posts

    Re: cursor in load from cursor

    ‏2013-01-03T08:34:26Z  
    • mwandishi
    • ‏2013-01-02T16:34:22Z
    Hi,

    Here is a non-LOAD CLP example:

    Disable autocommit:

    export DB2OPTIONS='+a -c +ec'
    db2 list command options

    Command Line Processor Option Settings

    Backend process wait time (seconds) (DB2BQTIME) = 1
    No. of retries to connect to backend (DB2BQTRY) = 60
    Request queue wait time (seconds) (DB2RQTIME) = 5
    Input queue wait time (seconds) (DB2IQTIME) = 5
    Command options (DB2OPTIONS) = +a -c +ec

    Option Description Current Setting



    -a Display SQLCA OFF
    -c Auto-Commit OFF <<<===
    ......

    Without hold example:

    db2 "DECLARE c1 CURSOR FOR SELECT tabname FROM syscat.tables"
    DB20000I The SQL command completed successfully.

    db2 "OPEN c1"
    DB20000I The SQL command completed successfully.

    db2 "FETCH FROM c1"

    TABNAME

    ACT

    1 record(s) selected.

    db2 "FETCH FROM c1"

    TABNAME

    ADEFUSR

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CATALOG

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CL_SCHED

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CUSTOMER

    1 record(s) selected.

    db2 commit
    DB20000I The SQL command completed successfully.

    db2 "FETCH FROM c1"
    DB21030E The cursor "C1" has not been opened.
    --Closed due to commit and not with hold.
    With hold example:
    db2 "DECLARE c1 CURSOR with hold FOR SELECT tabname FROM syscat.tables "

    db2 "OPEN c1"

    db2 "FETCH FROM c1"

    TABNAME

    ACT

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    ADEFUSR

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CATALOG

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CL_SCHED

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    CUSTOMER

    1 record(s) selected.
    db2 "FETCH FROM c1"

    TABNAME

    DEADREP

    1 record(s) selected.

    db2 commit

    db2 "FETCH FROM c1"

    TABNAME

    DEPARTMENT

    1 record(s) selected.

    db2 "FETCH FROM c1"

    TABNAME

    DEPT

    1 record(s) selected.

    --We can still fetch despite the commit.

    In your LOAD scenario the autocommit will close the cursor as it wasn't defined 'with hold'. It is basically all about the CLP.

    I hope this additional info helps?

    Best regards,
    Stephen Levett.
    Thank you mwandishi,

    autocommit is set to "on":

    -c Auto-commit ON

    This thing from example, it works: I declare a cursor and I can load from it, exactly what I declare, no more, no less.
    But what I'm thinking here, when I hit "enter" and autocommit is set to on, does it not commit?
    Here is what I mean:

    db2 "declare C3PO cursor for select * from ...... where ...." <enter><commit>

    time db2 load from C3PO of cursor insert into ..... nonrecoverable <enter><commit>

    I didn't define with or without hold, so "WITHOUT HOLD" should be default.
  • mwandishi
    mwandishi
    47 Posts

    Re: cursor in load from cursor

    ‏2013-01-03T11:26:10Z  
    • Noh.Aonne
    • ‏2013-01-03T08:34:26Z
    Thank you mwandishi,

    autocommit is set to "on":

    -c Auto-commit ON

    This thing from example, it works: I declare a cursor and I can load from it, exactly what I declare, no more, no less.
    But what I'm thinking here, when I hit "enter" and autocommit is set to on, does it not commit?
    Here is what I mean:

    db2 "declare C3PO cursor for select * from ...... where ...." <enter><commit>

    time db2 load from C3PO of cursor insert into ..... nonrecoverable <enter><commit>

    I didn't define with or without hold, so "WITHOUT HOLD" should be default.
    Hi Noh.Aonne,

    In that case I would expect the following to be returned if you tried to fetch from C3PO after the LOAD ( snip ):

    db2 "declare C3PO cursor for select * from employee"

    db2 "load from C3PO of cursor insert into EMPDUP nonrecoverable"
    SQL1193I The utility is beginning to load data from the SQL statement "
    select * from employee".
    .....

    SQL3515W The utility has finished the "LOAD" phase at time "03/01/2013
    11:22:56.521445".
    .....

    db2 "fetch from C3PO"
    DB21030E The cursor "C3PO" has not been opened.

    This is because autocommit has implicitly closed the without hold C3PO cursor.

    Best regards,
    Stephen Levett.
  • Noh.Aonne
    Noh.Aonne
    29 Posts

    Re: cursor in load from cursor

    ‏2013-01-03T13:43:26Z  
    • mwandishi
    • ‏2013-01-03T11:26:10Z
    Hi Noh.Aonne,

    In that case I would expect the following to be returned if you tried to fetch from C3PO after the LOAD ( snip ):

    db2 "declare C3PO cursor for select * from employee"

    db2 "load from C3PO of cursor insert into EMPDUP nonrecoverable"
    SQL1193I The utility is beginning to load data from the SQL statement "
    select * from employee".
    .....

    SQL3515W The utility has finished the "LOAD" phase at time "03/01/2013
    11:22:56.521445".
    .....

    db2 "fetch from C3PO"
    DB21030E The cursor "C3PO" has not been opened.

    This is because autocommit has implicitly closed the without hold C3PO cursor.

    Best regards,
    Stephen Levett.
    Hi Stephen,

    I'm not sure what you are saying here, that cursor closes after load? Because, when I just declare cursor and try to fetch from it I get this:

    /home/db2inst1> db2 "declare C3PO cursor for select * from xyz"
    DB20000I The SQL command completed successfully.
    /home/db2inst1> db2 "fetch from C3PO"
    DB21030E The cursor "C3PO" has not been opened.

    Best regards,
    NA.
  • mwandishi
    mwandishi
    47 Posts

    Re: cursor in load from cursor

    ‏2013-01-03T14:19:13Z  
    • Noh.Aonne
    • ‏2013-01-03T13:43:26Z
    Hi Stephen,

    I'm not sure what you are saying here, that cursor closes after load? Because, when I just declare cursor and try to fetch from it I get this:

    /home/db2inst1> db2 "declare C3PO cursor for select * from xyz"
    DB20000I The SQL command completed successfully.
    /home/db2inst1> db2 "fetch from C3PO"
    DB21030E The cursor "C3PO" has not been opened.

    Best regards,
    NA.
    Hi NA,

    That is the behaviour I was explaining. With autocommit=on the cursor will be closed after the declare from the CLP and so hence the: DB21030E
    The LOAD will do something similar, again because of autocommit=on.

    With respect to LOAD and your original question, the cursor will be closed because of: without hold and autocommit=on. You therefore don't have to be concerned about resources lying around after the LOAD.

    The Other CLP examples I provided where just to illustrate how the CLP works with respect to cursors and autocommit.

    As I wrote before if you want to be able to scroll through the C3PO cursor from the CLP you need to set automcommit=off. However, you don't need to do anything different for LOAD as it will handle the management of the cursor resource(s), you just need to declare it and it will close it when it has completed the LOAD.

    Best regards,
    Stephen Levett
  • Noh.Aonne
    Noh.Aonne
    29 Posts

    Re: cursor in load from cursor

    ‏2013-01-03T15:19:59Z  
    • mwandishi
    • ‏2013-01-03T14:19:13Z
    Hi NA,

    That is the behaviour I was explaining. With autocommit=on the cursor will be closed after the declare from the CLP and so hence the: DB21030E
    The LOAD will do something similar, again because of autocommit=on.

    With respect to LOAD and your original question, the cursor will be closed because of: without hold and autocommit=on. You therefore don't have to be concerned about resources lying around after the LOAD.

    The Other CLP examples I provided where just to illustrate how the CLP works with respect to cursors and autocommit.

    As I wrote before if you want to be able to scroll through the C3PO cursor from the CLP you need to set automcommit=off. However, you don't need to do anything different for LOAD as it will handle the management of the cursor resource(s), you just need to declare it and it will close it when it has completed the LOAD.

    Best regards,
    Stephen Levett
    Hi Stephen,

    thank You for your patience and explanations!

    Best regards!
  • mwandishi
    mwandishi
    47 Posts

    Re: cursor in load from cursor

    ‏2013-01-03T16:54:59Z  
    • Noh.Aonne
    • ‏2013-01-03T15:19:59Z
    Hi Stephen,

    thank You for your patience and explanations!

    Best regards!
    Hi NA,

    No problem at all.

    Best regards,
    Stephen Levett.