DB2 Iseries SQL failing to update multiple records

I am teaching myself sql so I may be asking an elementary question. Please forgive my ignorance.

I have this code :
delete from  VD_GPL.AGNITEMALLOCATIONKEY;


create table qtemp.items(
itemallocationid char(10) default ' '
,itemId char(20) default ' '
,displayproductnumber char(30) default ' '
,INVENTDIM_CONFIGID char(15) default ' '
,dataareaid char(4) default ' ' 
,locationdescription char(30) default' '
,toteinsertid char(10) default '  '
,totesid char(10) default ' '
,unitpertote integer default 0
,configid char(15) default ' '
,inventsiteid char(10) default ' '
,somiitemid char(20) default ' '
,imprfg char(3) default ' '
,rtrcode char(9) default ' ' 
,imsts char(1) default ' ' 
,imtrnr char(10) default ' '
,lmpn char(15) default ' '
,lmcode char(9) default ' '
,imco char(1) default ' ' 
);


insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,imco)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco in('1') and lmco in('1') AND
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);

insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,imco)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco in('3') and lmco in('3') AND
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);

insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,imco)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco in('5') and lmco in('5') AND
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);

insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,imco)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco in('8') and lmco in('8') AND
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);
 
select * from qtemp.items where displayproductnumber like '%00-047-89%' ; 


UPDATE qtemp.items a
SET locationdescription =   
                           			  (SELECT MIN(SUBSTR(AX_DESC,1,5)) 
                                      FROM   VD_GPL.AX_SHORT_DESC
                                        INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.imco = lmco);
                                      
UPDATE qtemp.items a
SET locationdescription =                                       
                                      trim(LOCATIONDESCRIPTION) CONCAT ' : ' CONCAT
                                      (SELECT SUBSTR(lmsdes,1,10) 
                                      FROM   kbm400mfg.fklocmst 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = lmcode and a.imco = lmco);
                                 
                                      
UPDATE qtemp.items a
SET locationdescription = 

					CASE
						WHEN        (SELECT MIN(locationdescription) FROM QTEMP.ITEMS  B
						                                  INNER JOIN KBM400MFG.FKLOCMST C ON A.IMCO = C.LMCO AND C.LMPN = trim(A.displayproductnumber)
						                                  WHERE A.LMCODE = C.LMCODE AND A.IMCO = C.LMCO AND C.LMPN = trim(A.displayproductnumber))  = ' '
	            		THEN        (SELECT MIN(SUBSTR(LMCODE,1,5)) FROM KBM400mfg.FKLOCMST D 
	            		                                      WHERE a.lmcode = D.lmcode and D.lmpn = trim(A.displayproductnumber) and D.lmco = a.imco)
				    END;
						                 

Open in new window


What I am trying to achieve here is this: if the first two updates to the Locationdescription field in the temporary file qtemp.items
does not populate the field, then populate the field with the fields LMCODE & LMSDES.  Now I realize that there is not code for the later this is because I have been trying to get the LMCODE to populate first.  I have been trying to problem solve, one small part at a time.  

I am running this in DB2 ISERIES SQL and it is very CPU intensive on the AS400, it causes it to run at 98%. It runs a long time and sucks the CPU up like mad.  I have not allowed it to go to completion yet since it is such a heavy hitter that it starts to impact the business workload and of course that it not good at all.  I cannot run this on our development box as the data is woefully out of date and the AX* files do not exist there, and the is a very small processor there, but that is another matter altogether.

Can someone help me to understand what I need to fix in this code (Specifically the last UPDATE) that can make the code run efficently and not run the CPU usage so high AND achive popuating the code (LMCODE) and desccription (LMSDES) when the first two UPDATE fail to populate the LocationDescription field?

I am attaching a input table showing two records that would be processing in this code and the results I am getting prior to the third update statement.


My thanks in advance for help to resolve these issues.
input.xlsx
result.xls
LeogalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LeogalAuthor Commented:
More detail, Job log details showing my join is not correct :

                        Additional Message Information                        
                                                                              
Message ID . . . . . . :   SQL0199       Severity . . . . . . . :   30        
Message type . . . . . :   Diagnostic                                         
Date sent  . . . . . . :   04/17/15      Time sent  . . . . . . :   10:20:22  
                                                                              
Message . . . . :   Keyword JOIN not expected. Valid tokens: , FROM INTO.     
Cause . . . . . :   The keyword JOIN was not expected here.  A syntax error   
  was detected at keyword JOIN.  The partial list of valid tokens is , FROM   
  INTO. This list assumes that the statement is correct up to the unexpected  
  keyword.  The error may be earlier in the statement but the syntax of the   
  statement seems to be valid up to this point.                               
Recovery  . . . :   Examine the SQL statement in the area of the specified    
  keyword.  A colon or SQL delimiter may be missing. SQL requires reserved    
  words to be delimited when they are used as a name. Correct the SQL         
  statement and try the request again.                                        
                                                                              
                                                                              

Open in new window

0
Dave FordSoftware Developer / Database AdministratorCommented:
Have you tried running your code through the Visual Explain tool in System i Navigator? That would help A LOT in explaining exactly what the query optimizer is doing to run your queries. It also points out where performance bottlenecks exist.

I admit that it does take a bit of training to learn how the read the results, but once you learn it, you'll wonder how you ever lived without it.

HTH,
DaveSlash
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeogalAuthor Commented:
@DaveSlash, I am trying to use the Visual Explain tool.  It looks like it is good, but I cannot afford to spend to much time in it at this time, I am on a tight deadline to deliver.  I modified the code for the third update to this:

 
UPDATE qtemp.items a
SET locationdescription = 

Case 	
when   (SELECT locationdescription WHERE LOCATIONDESCRIPTION =  ' ')
then     (select lmcode concat ' : ' concat lmsdes 
 from kbm400mfg.fklocmst b
 where b.lmco = a.imco and b.lmcode = a.lmcode and b.lmpn = trim(a.displayproductnumber) )
END;         

Open in new window


and received this error:

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token LOCATIONDESCRIPTION was not valid. Valid tokens: , FROM INTO. Cause . . . . . :   A syntax error was detected at token LOCATIONDESCRIPTION.  Token LOCATIONDESCRIPTION is not a valid token.  A partial list of valid tokens is , FROM INTO.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token LOCATIONDESCRIPTION. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

Open in new window


Although the code breezed through when I ran it through Visual Explain and Run it failed here and did not return a Visual Explain diagram.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Kent OlsenData Warehouse Architect / DBACommented:
Hi Leogal,

For this kind of testing, I suggest that you create tables that resemble your production tables and populate them with a sampling of the data.  Perhaps 100 rows.  Then you can write and test your SQL without impacting the entire database and response times.

It's generally considered poor form to run successive UPDATE statements to update the same column.  (That said, there are also times when that is the best approach, but it's generally better to write a single UPDATE statement.)

Also note that each of your 3 UPDATE statements will update every row in the table, meaning that the results of the first 2 will be overwritten.  To update certain rows, the UPDATE statement will need to be filtered (WHERE clause).

One more thing.  The CASE syntax returns a single value (per row).  If there is no clause (WHEN or ELSE) in the case statement applicable to the current row, NULL is returned.  So in the last UPDATE statement, rows that don't match the WHEN clause will be set to NULL.


Kent
0
Dave FordSoftware Developer / Database AdministratorCommented:
I've never used a SELECT statement inside of a WHEN clause of a CASE statement. I wonder if that's even valid. I'll have to test more.

Overall, though, you'll make your own life MUCH easier if you format your SQL in a somewhat "intuitive" and legible way ... with "proper" indenting. That makes your code much easier to understand (and debug later).

HTH,
DaveSlash
0
Dave FordSoftware Developer / Database AdministratorCommented:
Also, since I don't know the layout of your database, I am somewhat speculating here, but I suspect the performance problem has to do with a lack of useful indexes. That's one of those things Visual Explain can point out.

HTH,
DaveSlash
0
Dave FordSoftware Developer / Database AdministratorCommented:
I could be wrong here, but i think whatever follows the WHEN clause has to evaluate to true or false,

I don't think this evaluates to true or false:

(SELECT locationdescription WHERE LOCATIONDESCRIPTION =  ' ')
0
LeogalAuthor Commented:
@Kdo (Kent)  If I am reading your statements correctly, I need to use one case statement with perhaps two WHEN statements?  I did so and combined the 3 update statements into one case statement, that code looks like this:

UPDATE qtemp.items a
SET locationdescription =  
 
 CASE
                              WHEN
                                      (SELECT MIN(SUBSTR(AX_DESC,1,5)) 
                                      FROM   VD_GPL.AX_SHORT_DESC
                                        INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.imco = lmco) > ' '  
                                  THEN
                                      (SELECT MIN(SUBSTR(AX_DESC,1,5)) 
                                      FROM   VD_GPL.AX_SHORT_DESC
                                        INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.imco = lmco) 
                                          ||' : '||
                                     (SELECT SUBSTR(lmsdes,1,10) 
                                      FROM   kbm400mfg.fklocmst 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = lmcode and a.imco = lmco)
                               WHEN
                                       (SELECT MIN(SUBSTR(AX_DESC,1,5)) 
                                      FROM   VD_GPL.AX_SHORT_DESC
                                        INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.imco = lmco) =' '
                                 THEN 
                                 	  (SELECT lmcode
                                      FROM kbm400mfg.fklocmst b  
                                      WHERE b.lmpn = trim(a.displayproductnumber) 
                                        AND b.lmcode = a.lmcode)
                                         ||' : '||
                                     (SELECT SUBSTR(lmsdes,1,10) 
                                      FROM   kbm400mfg.fklocmst 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = lmcode and a.imco = lmco)                                     
 
					  END;       
					  
select * from qtemp.items order by ItemId;
select * from qtemp.items where itemid = '00-047-89';

Open in new window



Much better I think , except that the results are still the same as the attachment..
0
Kent OlsenData Warehouse Architect / DBACommented:
DB2 supports 2 forms of the CASE statement.

CASE (identifier)
WHEN value1 THEN
WHEN value2 THEN
...
ELSE
END

and

CASE
WHEN (logical condition1) THEN
WHEN (logical condition2) THEN
...
ELSE
END

The SQL that you posted doesn't conform to either of those so there's work to do there.

Can you explain what the selection/update criteria is?
0
LeogalAuthor Commented:
@kdo Please accept my apologies for my inept attempt.

 I am trying to achieve the following:

CASE
WHEN AX_DESC FOUND (NOT = '  '
THEN POPULATE THE LOCATIONDESCRIPTION FIELD WITH :
       AX_DESC  (A CODE LIKE 510ST)  CONCAT : CONCAT LOCATION DESCRIPTION (LMSDES)
     RESULT (510ST : WASA12-C-8)

WHEN AX_DESC NOT FOUND ( = '  ' )
THEN POPULATE THE LOCATIONDESCRIPTION FIELD WITH :
      LMCODE (A CODE LIKE 510ST) CONCAT : CONCAT LOCATION DESCRIPTION (LMSDES)
       RESULT (RM0SMI : WAS-13-C)

ELSE  LMCODE (510SX)

END
0
LeogalAuthor Commented:
here is what the AX_DESC file looks like :

(KBM_DESC)        (AX_DESC)
KBM Short             AX Short  
Description           Description
ZINC1                      ZINC1      
COL/X                     XROADS    
XROAD                    XROAD      
WIL/X                       XROAD      
WIL                          WIL        
WILL                        WIL        
WILEC                     WIL        
WL1LJ                    WIL        
WHSE                    WHSE      

both fields are char(20) bu the actual data is only a maximum of Char(5).
if there is not a hit on the KBM_DESC then I must pull the code & description from FKLOCMST
0
Kent OlsenData Warehouse Architect / DBACommented:
How many rows are in the items table?

How many rows are in fklocmst?
0
LeogalAuthor Commented:
Rows in Items, 72,376
Rows in Fklockmst, 293,446
0
Kent OlsenData Warehouse Architect / DBACommented:
If the tables are properly indexed, updating only 72,000 rows should be quick.

The SQL suggests that AX_DESC and LMCODE are both in the items table and that you're first checking AX_DESC and then LMCODE.  Is that correct?

The SQL also suggests that you're checking for blank to determine found/not found.  Are you really checking for blank or will the value be NULL?  Also, is it guaranteed that if the value is non-blank that it will occur in FKLOCMST?
0
LeogalAuthor Commented:
@kdo, with this new code it runs very fast.    I am checking the AX_DESC first if it is blank then I go and select the LMCODE. That is correct.  I am also using blank /non blank for found or not found.  the items will always be in FKLOCMST but the code translation may not exist in the AX_SHORT_DESC file.


UPDATE qtemp.items a
SET locationdescription =  
 
 CASE
 WHEN
             (SELECT MIN(trim(AX_DESC)) 
              FROM   VD_GPL.AX_SHORT_DESC
              INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
               WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.lmco = lmco) > ' '  
            
THEN
             (SELECT MIN(trim(AX_DESC)) 
              FROM   VD_GPL.AX_SHORT_DESC
              INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
               WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.lmco = lmco) 
               ||' : '||
              (SELECT SUBSTR(lmsdes,1,10) 
              FROM   kbm400mfg.fklocmst 
              WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = lmcode and a.lmco = lmco)
 
ELSE
               (SELECT trim(lmcode) FROM kbm400mfg.fklocmst b  
                WHERE b.lmpn = trim(a.displayproductnumber) and a.lmco = b.lmco and b.lmcode = a.lmcode)
                ||' : '||
                 (SELECT SUBSTR(lmsdes,1,10) FROM   kbm400mfg.fklocmst b
                    WHERE  lmpn = trim(a.displayproductnumber) and a.lmco = b.lmco and a.lmcode = b.lmcode)                                     
 
END;       
					  

Open in new window


I am doubling checking the results file. I am now finally getting both the items entries populated with the appropriate code & description on the sample. now to check the rest ....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.