Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
8 replies Latest Post - ‏2013-01-03T16:54:59Z by mwandishi
Noh.Aonne
Noh.Aonne
29 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: cursor in load from cursor

    ‏2013-01-02T16:07:41Z  in response to Noh.Aonne
    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
      ACCEPTED ANSWER

      Re: cursor in load from cursor

      ‏2013-01-02T16:34:22Z  in response to mwandishi
      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
        ACCEPTED ANSWER

        Re: cursor in load from cursor

        ‏2013-01-03T08:34:26Z  in response to mwandishi
        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
          ACCEPTED ANSWER

          Re: cursor in load from cursor

          ‏2013-01-03T11:26:10Z  in response to Noh.Aonne
          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
            ACCEPTED ANSWER

            Re: cursor in load from cursor

            ‏2013-01-03T13:43:26Z  in response to mwandishi
            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
              ACCEPTED ANSWER

              Re: cursor in load from cursor

              ‏2013-01-03T14:19:13Z  in response to Noh.Aonne
              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
                ACCEPTED ANSWER

                Re: cursor in load from cursor

                ‏2013-01-03T15:19:59Z  in response to mwandishi
                Hi Stephen,

                thank You for your patience and explanations!

                Best regards!
                • mwandishi
                  mwandishi
                  47 Posts
                  ACCEPTED ANSWER

                  Re: cursor in load from cursor

                  ‏2013-01-03T16:54:59Z  in response to Noh.Aonne
                  Hi NA,

                  No problem at all.

                  Best regards,
                  Stephen Levett.