Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Set the number of rows Using Utility

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
chockalingam_rsp

New User


Joined: 13 Aug 2009
Posts: 43
Location: chennai

PostPosted: Mon Nov 21, 2011 10:08 am    Post subject: Set the number of rows Using Utility
Reply with quote

Hi ,

I am Using a DB2 Utility to extract the table to a flat file.

Code:
UNLOAD                                                     
TABLESPACE DMSD0020.DMST0090                               
         UNLDDN U001                                       
         SHRLEVEL CHANGE                                   
         FROM TABLE DBA.DMSTBUSINESS_NAME                 
         (DUNS_NBR                       INTEGER           
         ,BUS_NME                        CHAR(30)         
         ,DT_LAST_UPD                    DATE EXTERNAL     
         ,BUS_NME_CMPLT                  CHAR(90)         
         ,BUS_NME_INVRD                  CHAR(30)         
         ,BUS_NME_REV_DT                 DATE EXTERNAL     
         ,BUS_NME_USG_CD                 CHAR(1))         


Using this , i get the whole table being extract.

My requirement is, I need to fetch only the first 1000 rows of the above table. Can you please help in this.
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Nov 21, 2011 11:24 am    Post subject:
Reply with quote

You can use the limit parameter

LIMIT integer Specifies the maximum number of rows that are to be
unloaded from a table. If the number of unloaded rows
reaches the specified limit, message DSNU1201 is issued for
the table, and no more rows are unloaded from the table.
The process continues to unload qualified rows from the
other tables.

When partition parallelism is activated, the LIMIT option
is applied to each partition instead of to the entire
table.

integer
Indicates the maximum number of rows that are to be
unloaded from a table. If the specified number is less
than or equal to zero, no row is unloaded from the
table.

Like the SAMPLE option, if multiple tables are unloaded
with the LIMIT option, the referential integrity between
the tables might be lost.
Back to top
View user's profile Send private message
chockalingam_rsp

New User


Joined: 13 Aug 2009
Posts: 43
Location: chennai

PostPosted: Mon Nov 21, 2011 11:27 am    Post subject:
Reply with quote

could you please give me the syntax , coz when i tried this option it gave me error stating that "KEYWORD 'LIMIT' INCOMPATIBLE WITH OTHER KEYWORDS OR OPERANDS "

I gave it as following

Code:
        ,BUS_NME_REV_DT                 DATE EXTERNAL   
        ,BUS_NME_USG_CD                 CHAR(1))       
        LIMIT 1000
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Nov 21, 2011 1:12 pm    Post subject:
Reply with quote

Quote:
I need to fetch only the first 1000 rows of the above table
Without an "ORDER BY", "first nnnn" does not make much sense from DB2 per se . If you use an ORDER BY in the SQL query you'll fetch the first 1000 of the result-set. Having said that, please check if Fetching a limited number of rows: FETCH FIRST n ROWS ONLY works for you.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1537
Location: Andromeda Galaxy

PostPosted: Mon Nov 21, 2011 1:30 pm    Post subject:
Reply with quote

chockalingam_rsp,

Why do prefer to do it using UNLOAD rather using DSNTIAUL?
Is that because of client requirement or something?? Curious to know.
Back to top
View user's profile Send private message
db2sysdba.zos

New User


Joined: 04 Oct 2011
Posts: 25
Location: INDIA

PostPosted: Mon Nov 21, 2011 1:55 pm    Post subject:
Reply with quote

Hi chockalingam_rsp,

Code:
UNLOAD                                                     
TABLESPACE DMSD0020.DMST0090                               
         UNLDDN U001                                       
         SHRLEVEL CHANGE                                   
         FROM TABLE DBA.DMSTBUSINESS_NAME  LIMIT integer       
         (DUNS_NBR                       INTEGER           
         ,BUS_NME                        CHAR(30)         
         ,DT_LAST_UPD                    DATE EXTERNAL     
         ,BUS_NME_CMPLT                  CHAR(90)         
         ,BUS_NME_INVRD                  CHAR(30)         
         ,BUS_NME_REV_DT                 DATE EXTERNAL     
         ,BUS_NME_USG_CD                 CHAR(1))


Try this syntax....
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10276
Location: italy

PostPosted: Mon Nov 21, 2011 2:27 pm    Post subject: Reply to: Set the number of rows Using Utility
Reply with quote

did anybody notice that Your posts were edited to add the code tags
in order to make easier for people willing to help to read and understand the posted code icon_question.gif
do it Yourself when You post not to waste the moderators time to do it on Your behalf icon_evil.gif

code tags and preview will keep everybody happy icon_cool.gif
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 How to Use DebugTool CodeCoverage Uti... neo4u IBM Tools 0 Thu Jun 22, 2017 6:38 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts Search utility(3.14) using JCl and Cobol RKS3 COBOL Programming 12 Sat Jun 10, 2017 10:24 pm
No new posts DB2 - TERMINATE utility vasanthz DB2 5 Mon May 15, 2017 11:49 pm
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us