Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Unanswered: Creating a cursor in a script

    I have particular records in my db that I need to cross-reference and the only way I can think of doing this is by using a cursor executing dynamic SQL.

    I want to do this in a single script I can run but the problem I have is the only examples I can find are for creating procedures but I don't want to do this because this is just something I need to run on the client's database from time to time to diagnose problems. Any idea how I can do this without creating a procedure?

  2. #2
    Join Date
    Dec 2008
    Posts
    76
    why not just create the procedure, use it, then drop it?
    RD

  3. #3
    Join Date
    Jul 2009
    Posts
    5
    Because the user would have to give "create" access which they don't want to do (they're a bank)

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Could you explain what exactly the cross-referencing shall do? Do you want to change (insert/update/delete) some data or do you just want to retrieve a result set returning some differences or erroneous records?

    You typically don't need a cursor for that - just use some regular SQL statements that express your conditions and then work with the result set in whichever way you want.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jul 2009
    Posts
    5
    Basically the way our app has been designed is that rather than allowing the db to decide the next primary key for each table these are stored in a seperate table containing the table name and key (I know...not my idea but something I'm stuck with!! ).

    Sometimes these get out of sync so when the application tries to insert a new record it does so with an invalid primary key. What I need to do is loop through each of the records on the table and check the actual primary key value (using dynamic sql) to see where the 2 are out of sync. Unfortunately I don't have the option of changing how the application does this but what I just need to run this on an adhoc basis to see where there might be some problems (hope this makes sense)

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by petergriffin

    Sometimes these get out of sync so when the application tries to insert a new record it does so with an invalid primary key.
    Invalid here means a duplicate value, because the next value in the key lookup table is less than the maximum PK value in the data table?

    Code:
    select * from datatable where pk >  
    (select nextpk from keylookup where tablename = 'DATATABLE')
    ?
    Last edited by n_i; 07-22-09 at 17:49.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jul 2009
    Posts
    5
    Quote Originally Posted by n_i
    Invalid here means a duplicate value, because the next value in the key lookup table is less than the maximum PK value in the data table?

    Code:
    select * from datatable where pk >  
    (select nextpk from keylookup where tablename = 'DATATABLE')
    ?
    That's exactly the problem, unfortunately this is something that's in the application code which I don't have access to, what I just need to do is to occasionally run a script which will identify those keys which have gotten out of sync so they can be repaired (either manually or as part of the script)

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    So why not just execute this query as a SQL script?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jul 2009
    Posts
    5
    That's exactly what I want to do but I need to loop through each record in the PKs table to check what the corresponding PK on the datatable is (was planning on doing this using dynamic SQL). The problem I'm having is in creating the cursor to loop through each of these records - just neater than having to manually create each select manually as it makes sure I don't miss any.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How'bout this:
    Code:
    db2 -x "select 
    'select', ''''||tablename||'''', ', pk from', tablename, 
    'where pk > (select nextpk from keylookup where tablename = ''' || tablename || ''')' 
    from keylookup" | db2
    Obiously, all this should be on one line - I split it up for better readability.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by petergriffin
    That's exactly what I want to do but I need to loop through each record in the PKs table to check what the corresponding PK on the datatable is (was planning on doing this using dynamic SQL). The problem I'm having is in creating the cursor to loop through each of these records - just neater than having to manually create each select manually as it makes sure I don't miss any.
    Why do you think you need to loop? Simply join the 2 tables and be done with it - exactly as n_i posted.

    p.s: SQL was not designed as a procedural language (originally). So don't try to cram it into this corset. If you do things the SQL-way (set oriented), you are ofter much better off.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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