View previous topic :: View next topic
|
Author |
Message |
rajesh1183
New User
Joined: 07 Jan 2008 Posts: 98 Location: Hyderabad
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
How many rows are in the DB2LIB.T_CLAIMS table? |
|
Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008 Posts: 98 Location: Hyderabad
|
|
|
|
Quote: |
It updates around 3 crore records in table T_CLAIMS
|
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
rajesh1183
New User
Joined: 07 Jan 2008 Posts: 98 Location: Hyderabad
|
|
|
|
Is there any way that I can use DB2 CURSOR technique using SAS program...?? |
|
Back to top |
|
|
|