Solved

How can I tweak this SQL to perform DB2 update rather than listing

Posted on 2015-01-19
17
271 Views
Last Modified: 2015-02-17
Hello Gurus,
The SQL code found in this thread when executed with the last select in the code, produces the expected results as shown beloow:
But when I changed the last select to
update a specific substring
, I get errors
                     

 Could someone show me how I  can change this script from doing a select statement to doing update to all records that qualify. The SQL code works fine when executed with a select. Now I want thisame code to update the rable instead.

This is of utnost importance,  

 WITH strings (text)
AS
(
  SELECT memo as text from periscope.product_trans_log

),
patstrings (text)
as
(
  SELECT case when locate_in_string (text, 'WGT') = 0 then '0.00'
              else substr (text, locate_in_string (text, 'WGT'))
         end
  FROM strings
),
targetstrings (text)
as
(

  SELECT substr (case when locate (',', text) = 0 then text
                 else left (text, locate (',', text)-1)
                 end, 4)
  FROM patstrings
)
select periscope.product_trans_log
 case length (text) 
            when 0 then '0.00'
            when 1 then '0.0' || text
            when 2 then '0.' || text
            else left (text, length (text) - 2) || '.' || right (text, 2)
   end
from targetstrings 

Open in new window

3.19
 0.66
 0.61
 1.21
 1.49
 0.61
 1.00
 1.00
 0.00
 0.00
 5.85
 0.00
 0.49
 1.04
 1.00
 0.00
 0.00
 0.69
 1.40
 1.54
 2.28
 2.44
 2.97
 1.19
 2.53
 0.00
 0.00
 0.35
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.01
 0.01
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 0.00
 1.20
 0.00
 0.00
 0.00
 0.00
 0.76
 1.00
 0.95
 2.76
 0.81
 0.26
 0.53
 0.23
 0.00
 0.00
 0.00
 1.00
 1.00
 1.00
 0.01
 0.55
 0.00
 0.00
 0.00
 0.00
 0.03
 0.00
 0.00
 1.03
 0.00
 2.51
 1.27
 1.62
 0.58
 1.61
 1.32
 1.19
 1.79
 1.61
 1.47
 0.00
 0.00
 0.00
 0.84
 1.14
 0.00
 0.00
 0.00
 1.47
 1.23
 0.01
 0.03

Open in new window

0
Comment
Question by:Enyimba
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40559094
Instead of using common table expressions, simply "nest" the various layers as a conventional select query. This should be much easier to translate into an update query.

SELECT ID, -- include the unique identifier
      CASE LENGTH(memo)
            WHEN 0 THEN '0.00'
            WHEN 1 THEN '0.0' + memo
            WHEN 2 THEN '0.' + memo
            ELSE LEFT(memo, LENGTH(memo) - 2) + '.' + RIGHT(memo, 2)
      END as targetstring
FROM (
            SELECT ID, -- include the unique identifier
                  SUBSTR(CASE
                        WHEN locate(',', memo) = 0 THEN memo
                        ELSE LEFT(memo, locate(',', memo) - 1)
                  END, 4) as memo
            FROM (
                        SELECT ID, -- include the unique identifier
                              CASE
                                    WHEN locate_in_string(memo, 'WGT') = 0 THEN '0.00'
                                    ELSE substr(memo, locate_in_string(memo, 'WGT'))
                              END as memo
                        FROM periscope.product_trans_log
                  ) PATSTRINGS
      ) TARGETSTRINGS
;

Open in new window


Before this could be converted to an update query it would need to include the unique record identifier (e.g. ID) all the way "up" that query. Then in the outer layer you should be able to correlate the calculated string to a record in the table and perform an update to the table.
0
 

Author Comment

by:Enyimba
ID: 40559381
PortletPaul

Thank you for the re-write of the original sql code. When I tried to test your code, it threw an error shown below. How can I correct this? Is there a way to tell what the invalid character may be?

An error occurred while processing the results. - Invalid character found in a character string argument of the function "DECFLOAT".. SQLCODE=-420, SQLSTATE=22018, DRIVER=3.68.61

I urgently need a solution to this problem and preferaby this morning.

Thank you

Enyimba
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40559404
My fault, Sorry. I replaced the concatenation double pipes with a plus symbol (just so I could format the query) and then forgot to reverse that edit.

Here it is without the plus symbols
SELECT ID, -- include the unique identifier
      CASE LENGTH(memo)
            WHEN 0 THEN '0.00'
            WHEN 1 THEN '0.0' || memo
            WHEN 2 THEN '0.' || memo
            ELSE LEFT(memo, LENGTH(memo) - 2) || '.' || RIGHT(memo, 2)
      END as targetstring
FROM (
            SELECT ID, -- include the unique identifier
                  SUBSTR(CASE
                        WHEN locate(',', memo) = 0 THEN memo
                        ELSE LEFT(memo, locate(',', memo) - 1)
                  END, 4) as memo
            FROM (
                        SELECT ID, -- include the unique identifier
                              CASE
                                    WHEN locate_in_string(memo, 'WGT') = 0 THEN '0.00'
                                    ELSE substr(memo, locate_in_string(memo, 'WGT'))
                              END as memo
                        FROM periscope.product_trans_log
                  ) PATSTRINGS
      ) TARGETSTRINGS
;

Open in new window

0
 

Author Comment

by:Enyimba
ID: 40559593
PortletPaul,

I just ran a test, which looked promising but when I did a select to see if the column subscript value was updated in the table, it came back still showing the original value the way it was. The sql listed the values as changed but did not update the table. See the results below:  Please see if you can correct why it is not updating.

SQL result displayed on the screen
ID      TARGETSTRING
 ------- ------------
 1386322 0.79
 1386323 0.98
 1386324 1.00
 1386325 1.00
 1386326 1.00
 1386327 0.04
 1386328 0.05
 1386329 0.05
 1386330 0.04
 1386331 0.00
 1386332 0.00
 1386333 0.01
 1386334 0.00
 1386335 0.00
 1386336 0.00
 1386337 0.00
 1386338 0.03
 1386339 0.00
 1386340 0.00
 1386341 9.94
 1386342 8.07
 1386343 7.03
 1386344 1.53
 1386345 1.74
 1386346 1.48
 1386347 1.25
 1386348 0.00
 1386349 0.00
 1386350 0.00
 1386351 0.00
 1386352 1.23

SQL select from table result displayed on the screen
1407869 KEY1742552,WGT100,QTY1
 1407871 KEY1742552,WGT5,QTY1
 1407872 KEY1742552,WGT2,QTY1
 1407880 KEY1742552,WGT100,QTY1
 1407881 KEY1742552,WGT173,QTY1
 1407882 KEY1742552,WGT69,QTY1
 1407883 KEY1742552,WGT67,QTY1
 1407884 KEY1742552,WGT37,QTY1
 1407890 KEY1742552,WGT2,QTY1
 1407894 KEY1742552,WGT2,QTY2
 1407895 KEY1742552,WGT1,QTY3
 1407896 KEY1742552,WGT234,QTY1
 1407897 KEY1742552,WGT185,QTY1
 1407898 KEY1742552,WGT177,QTY1
 1407899 KEY1742552,WGT183,QTY1
 1407900 KEY1742552,WGT254,QTY1
 1407901 KEY1742552,WGT238,QTY1
 1407902 KEY1742552,WGT301,QTY1
 1407903 KEY1742552,WGT1,QTY2
 1407904 KEY1742552,WGT1,QTY1
 1407925 KEY1742552,WGT135,QTY1
 1407926 KEY1742552,WGT136,QTY1
 1407927 KEY1742552,WGT250,QTY1
 1407928 KEY1742552,WGT119,QTY1
 1407929 KEY1742552,WGT100,QTY1
 1407930 KEY1742552,WGT100,QTY1
 1407931 KEY1742552,WGT100,QTY1
 1407932 KEY1742552,WGT100,QTY1
 1407933 KEY1742552,WGT120,QTY1
  1407938 KEY1742553,WGT4,QTY1
  1407942 KEY1742553,WGT46,QTY1
 1407946 KEY1742553,WGT181,QTY1
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40559777
Hi Enyimba,

This looks like a rework of the 'PAT' conversion that we did a week or so back.

You should be able to take that SQL, replace 'PAT' with 'WGT' and be very close to what you need....


Kent
0
 

Author Comment

by:Enyimba
ID: 40559853
Yes kent, it is the same SQL. I tried to get your attention earlier.
My problem now is that the SQL is not updating the db2 table.

Can you help?

thaks

Enyimba
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40559915
Hi Enyimba,

I took a holiday this past weekend.  Didn't even come close to a keyboard for about 3 days!

You seem to have several open questions on this problem right now.  I replied in another thread.  Take a look and see if that will solve the problem for you.


Kent
0
 
LVL 18

Expert Comment

by:daveslash
ID: 40560268
In the original post, you say, "I get errors", but exactly what errors are you getting?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40560975
I only provided a select query which needs alteration to an update.

I think DB2 update syntax allows the following:
UPDATE periscope.product_trans_log PTL
SET ( PTL.memo ) = 
    (
        SELECT
              CASE LENGTH(memo)
                    WHEN 0 THEN '0.00'
                    WHEN 1 THEN '0.0' || memo
                    WHEN 2 THEN '0.' || memo
                    ELSE LEFT(memo, LENGTH(memo) - 2) || '.' || RIGHT(memo, 2)
              END as targetstring
        FROM (
                    SELECT ID, -- include the unique identifier
                          SUBSTR(CASE
                                WHEN locate(',', memo) = 0 THEN memo
                                ELSE LEFT(memo, locate(',', memo) - 1)
                          END, 4) as memo
                    FROM (
                                SELECT ID, -- include the unique identifier
                                      CASE
                                            WHEN locate_in_string(memo, 'WGT') = 0 THEN '0.00'
                                            ELSE substr(memo, locate_in_string(memo, 'WGT'))
                                      END as memo
                                FROM periscope.product_trans_log
                          ) PATSTRINGS
              ) TARGETSTRINGS
       WHERE PTL.ID = TARGETSTRINGS.ID
   )
;

Open in new window

0
 

Author Comment

by:Enyimba
ID: 40561150
PortletPaul,

Thanks for your continuing interest and for providing a revised code. I will test it now and report on the result.

Enyimba
0
 

Author Comment

by:Enyimba
ID: 40561208
PortletPaul

After more than 30 minutes of trying to process 1.1 milion rows, the sql code/db2 threw the following error:
The transaction log for the database is full.. SQLCODE=-964, SQLSTATE=57011, DRIVER=3.68.61

Is there something you can do about this? I was thinking if the initial select query phase could be written to a temp file and the temp file subsequently read to do a direct read/write to the table? if that is feasible, can you show me how that can be accomplished? Any other suggestion you can think of?

Thanks

Enyimba
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40561308
is there anything that can be done?

a.
    Do it in smaller "chunks"
    Exactly how you arrive at those chunks I can't tell you
    e.g. category = 'a' then category = 'b' and so on

b.
    try to arrive at the required string manipulation without needing the nested queries
    (and hence avoid the need to match to ID)
0
 

Author Comment

by:Enyimba
ID: 40561357
Yes, I am doing in chunks now. However, doing it in monthly range, which is what I am doing but still gets errors, or it never finishes...

Please explain what you mean by
"...b.)  try to arrive at the required string manipulation without needing the nested queries (and hence avoid the need to match to ID)

Can you demonstrate with the code you already provided?

Thanks

Anyanso
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40561376
>>"Can you demonstrate with the code you already provided?"

if I could have I would have.

You haven't supplied BOTH "sample data" and "expected result"

and "sample data" should be "per table" (or view); NOT a result of a join

With those it might be possible to reduce the query complexity.

But I cannot guarantee it will be fast, or even faster than what you have, as I have no way of testing

{+edit}

what I mean is something like this...

update table1
set x = 'x' || x
where category = 'a'
;

i.e. all the string manipulation logic is simplified so that no other resultset or table is required by the query
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40561887
Hi Anyanso,

A couple of questions for you....  Is this the same issue that we're discussing in another thread.  If so, we need to consolidate both discussions into one place.

The transaction log for the database is full.. SQLCODE=-964, SQLSTATE=57011, DRIVER=3.68.61

I mentioned elsewhere that logging was going to be an issue.  Depending on the row size and blocking, the query could require more logging space than is available on your system.  It's almost certain to require more space than you have configured to DB2.

Are you working in a test environment or on a production database?

There is a way to skip logging, but it comes with considerable risk.  You'll want to have the database offline and you'll want to be 100% sure that no error will result.  Any SQL exception will leave the table corrupt and require reloading it from a backup.  (This feature is really intended for the initial load of a table, but it can be used any time.)

It looks like there's a considerable flaw in the SQL that you and Paul are working with here.  It will not replace the WGT substring with the intended edited result.  It appears that it will replace the entire string with just the edited WGT substring.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40563490
I am not familiar with the other question (a URL to it would be handy)

I would STRONGLY urge you to supply "sample data" and "expected result"; If, as kdo suggests there is something wrong with this update you don't want to be doing this multiple times and/or losing data.

I would expect you to be working in a dev environment until the query is proven.

In fact I suggest you want to start with a smaller sample table anyway, get the logic worked out, then try it on the large table (taking whatever approach you decide regarding logging).
0
 

Author Comment

by:Enyimba
ID: 40568740
All recommendation already being used viz, development, smaller chunks, etc...

Thanks

Enyimba
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now