Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Table design issue

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Table design issue
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    Post subject: Re: Table design issue
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SIGNAL ON HALT issue packerm CLIST & REXX 0 Fri Oct 20, 2017 6:56 pm
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us