IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Table design issue


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
eHorizon.Andrew

New User


Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications

PostPosted: Mon Jul 25, 2011 11:36 am
Reply with quote

I have a question about db2 table design, would anyone help me on this? Any advice will be appreciated.
Here is the issue:
The requirement is that I need set up a table, and both online and batch program would read this and get the necessary reference number, let¡¯s take account for example, there is a field in table called ACT-NBR-GNT, which stores the account number already been generated and other programs should read this for creating a new account (using this as a reference number in certain position of account field.) and update this field after account created (by increase 1 value to this field)
The question is when online program is doing this process, it takes a long time, and lock the table ( or specified row) to prevent other batch programs to go further, always time out error, so could any help me on this?

I have thought about separating the online and batch programs to read different rows in table, but how can I control the update process for this field as the max number should be unique, otherwise it will cause the same account number if the update process not finished through online program and bath one get the old value.

Thanks a lot!
Back to top
View user's profile Send private message
Stefan

Active User


Joined: 12 Jan 2006
Posts: 110
Location: Germany

PostPosted: Mon Jul 25, 2011 12:04 pm
Reply with quote

Instead of fiddling around with a numeric key within your application programs, I recommend the use of a sequence number.
With this DB2 feature it is the DB2 server itself who cares about a unique key and the increment logic.
You could define a sequence number by DDL, for example as follows:
Code:

SET CURRENT SQLID = 'MYOWNER';     
DROP   SEQUENCE MY_SEQUENCE_1;
COMMIT;                             
CREATE SEQUENCE MY_SEQUENCE_1
             AS INTEGER             
     START WITH 1                   
   INCREMENT BY 1                   
       MINVALUE 1                   
       MAXVALUE 2147483647         
       NO CYCLE                     
          CACHE 256                 
          ORDER;                   

Then you have to generate a unique key by modifying the INSERT statement. Instead of retrieving the existing key value, increasing it by +1, and using the new value as an argument in the INSERT statement, you now specify the new value with the phrase "next value for <sequence number>". Then DB2 generates a new value and ensures data integrity and smoothless concurrent access by all address spaces, e.g batch or CICS. This INSERT statement could then be included in a SELECT statement to retrieve the newly generated key as follows:
Code:

SELECT MY_KEY                                                 
  FROM FINAL TABLE                                             
            (INSERT INTO MYOWNER.MYTABLE                       
                        (MY_KEY                               ,
                         MY_TIMESTAMP                         ,
                         MY_NUMBER)'                           
              VALUES    (NEXT VALUE FOR MYOWNER.MY_SEQUENCE_1 ,
                         CURRENT TIMESTAMP                    ,
                         1)                                   
            )                                                 
FOR FETCH ONLY                                                 

Hope this helps
Back to top
View user's profile Send private message
eHorizon.Andrew

New User


Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications

PostPosted: Mon Jul 25, 2011 4:02 pm
Reply with quote

Thanks, Stefan, I would try this and reply on this later .
Back to top
View user's profile Send private message
eHorizon.Andrew

New User


Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications

PostPosted: Tue Jul 26, 2011 12:00 pm
Reply with quote

In your second code section, if the update process can be finished durning the select clause concurrently, that would work for my program, how could this happen in db select clause, Steafan? Thanks very much for your help.
Back to top
View user's profile Send private message
Stefan

Active User


Joined: 12 Jan 2006
Posts: 110
Location: Germany

PostPosted: Tue Jul 26, 2011 12:57 pm
Reply with quote

Detailed explanations for SELECT FROM FINAL TABLE could be found on the IBM Information Management Software for z/OS Solutions Information Center or on An Expert's Guide to DB2 Technology.

Hope this helps
Back to top
View user's profile Send private message
eHorizon.Andrew

New User


Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications

PostPosted: Tue Jul 26, 2011 3:09 pm
Reply with quote

That is so wonderful, thanks a lot again.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top