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
 
SAS: PROC SQL Update query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Sat Mar 14, 2009 3:27 pm    Post subject: SAS: PROC SQL Update query
Reply with quote

Hi,

I have a update query executing in a SAS program. It updates around 3 crore records in table T_CLAIMS ...Is there any way of fine tuning the below query..?? In SAS, it was not allowing me to retreive all the 3 required columns at a time but I should retreive individually...

DB2LIB is
Code:

LIBNAME DB2LIB DB2   
        SSID=D2U1   
        AUTHID="TEVN324";



Code:

PROC SQL;
UPDATE DB2LIB.T_CLAIMS A                             
   SET                                                         
   CLAIM_CODE   = (SELECT  B.SCHADEN_CODE                     
                     FROM  DB2LIB.T_BUSINESS_CODES B         
                    WHERE  A.BUSINESS_CENTER = B.BUSINESS_CENTER
                      AND  B.YEAR = 2009                         
                      AND  B.MONTH = 01)                         
   ,CNTRY_CODE  = (SELECT  B.CNTRY_CODE                         
                     FROM  DB2LIB.T_BUSINESS_CODES B         
                    WHERE  A.BUSINESS_CENTER = B.BUSINESS_CENTER
                      AND  B.YEAR = 2009                         
                      AND  B.MONTH = 01)                         
   ,CURR_CODE   = (SELECT  B.CURRENCY                     
                     FROM  DB2LIB.T_BUSINESS_CODES B         
                    WHERE  A.BUSINESS_CENTER = B.BUSINESS_CENTER
                      AND  B.YEAR = 2009                         
                      AND  B.MONTH = 01)                     
;
QUIT;


I was receiving the following error
Code:

ERROR: Error fetching from cursor. DB2 error is    DSNT408I SQLCODE = -905, ERROR:  UNSUCCESSFUL EXECUTION DUE TO RESOURCE LIMIT BEING EXCEEDED, RESOURCE NAME = ASUTIME LIMIT = 000000000013 CPU SECONDS  (000000600000 SERVICE UNITS) DERIVED FROM D2SA.DSNRLST01


Any clue..??
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Sat Mar 14, 2009 6:22 pm    Post subject:
Reply with quote

At an initial glance without access to documentation, it appears that it is a question of the job running over the CPU time allocated to the job class, as stated in the error messages.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Mar 14, 2009 7:38 pm    Post subject:
Reply with quote

Hello,

How many rows are in the DB2LIB.T_CLAIMS table?
Back to top
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Sun Mar 15, 2009 7:15 pm    Post subject:
Reply with quote

Quote:

It updates around 3 crore records in table T_CLAIMS
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Mar 15, 2009 11:34 pm    Post subject:
Reply with quote

Hello,

Sorry, i don't speak crore and misread that as a typo for "core".

Best to use English . . .

If i understand that a crore is 10 million, suggest there should not be a query that updates 30 million rows. . .

Suggest a better approach would be to wrap the query in code and control the volume of updates per query.
Back to top
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Mon Mar 16, 2009 11:14 am    Post subject: Reply to: SAS: PROC SQL Update query
Reply with quote

Is there any way that I can use DB2 CURSOR technique using SAS program...??
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 -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
This topic is locked: you cannot edit posts or make replies. PS file data should be passed as symb... d_sarlie JCL & VSAM 15 Tue Oct 03, 2017 5:18 am
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts LIBDEF not working in called proc packerm CLIST & REXX 0 Fri Sep 15, 2017 7:22 pm

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