sql error -199 sqlstate 42601

Ron Thomas

sql error -199 sqlstate 42601

Hello . I am executing the following sql to updated the timestamp of the latest ITEM_NBR in the table item_log

 UPDATE
B3ITEM.ITEM_LOG a
SET
a.LOG_TS = b.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_LOG b INNER JOIN (
SELECT
MAX(s2.log_ts) AS max_ts,
s2.item_nbr
FROM
B3ITEM.ITEM_LOG s2
GROUP BY
s2.item_nbr
) k1
ON b.item_nbr = k1.item_nbr and b.log_ts = k1.max_ts
WHERE
a.item_nbr = k1.item_nbr AND a.log_ts = k1.log_ts AND a.LAST_CHG_USER_ID = 'YUIO';

 

The above sql is throwing -199 and sqlstate 42601. Could someone let me know where the issue is in this sql ?

 SQL Error [42601]: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=FROM; QUERYNO WHERE SKIP WITH ??( [ CONCAT || /, DRIVER=3.69.24
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=FROM; QUERYNO WHERE SKIP WITH ??( [ CONCAT || /, DRIVER=3.69.24

Regards

Ron T

 

 

Edited By:
Ron Thomas[Organization Members] @ May 07, 2019 - 09:19 PM (America/Eastern)
Ron Thomas[Organization Members] @ May 07, 2019 - 11:03 PM (America/Eastern)

Ruediger Kurtz

AW: sql error -199 sqlstate 42601
(in response to Ron Thomas)
Ron,

I can’t say for sure without seeing the full error message but the combination of UPDATE … SET … FROM without SELECT somewhere in between strikes me as strange.

Rüdiger


Rüdiger Kurtz
Abteilung Informatik - Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas.
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Ron Thomas [mailto:[login to unmask email]
Gesendet: Mittwoch, 8. Mai 2019 02:50
An: [login to unmask email]
Betreff: [DB2-L] - sql error -199 sqlstate 42601


Hello . I am executing the following sql to updated the timestamp of the latest ITEM_NBR in the table item_log



UPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1

ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts

WHERE
a.item_nbr = k1.item_nbr and a.LAST_CHG_USER_ID = 'IT230X' ;



The above sql is throwing -199 and sqlstate 42601. Could someone let me know where the issue is in this sql ?



Regards

Ron T





-----End Original Message-----

Srinivas Adupa

sql error -199 sqlstate 42601
(in response to Ron Thomas)
Hey Ron,
Multiple perspectives here:
1) I believe, UPDATE will not accept FROM clause and that too for a column
that you are assigning
2) If Db2 accepts this, then don't you think the SQL is trying to keep
multiple values instead of 1 value only? In other words, what-if your inner
sql returns more than 1 row at a time.
Again, I still believe you dont need to enter till 2nd point, as the
problem might be in 1st point only.

Best Regards,
Srini.

On Wed, May 8, 2019 at 6:20 AM Ron Thomas <[login to unmask email]> wrote:

> Hello . I am executing the following sql to updated the timestamp of the
> latest ITEM_NBR in the table item_log
>
>
>
> UPDATE
> B3ITEM.ITEM_log a
> SET
> A.LOG_TS = a.LAST_UPDATE_TS
> FROM
> B3ITEM.ITEM_log b INNER JOIN (
> SELECT
> MAX(s2.log_ts) AS maxts, s2.item_nbr
> FROM
> B3ITEM.ITEM_log s2
> GROUP BY
> s2.item_nbr
> ) k1
>
> ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts
>
> WHERE
> a.item_nbr = k1.item_nbr and a.LAST_CHG_USER_ID = 'IT230X' ;
>
>
>
> The above sql is throwing -199 and sqlstate 42601. Could someone let me
> know where the issue is in this sql ?
>
>
>
> Regards
>
> Ron T
>
>
>
>
>
> -----End Original Message-----
>


--
*Thanks & Regards,*
*Srinivas Adupa*

bernd oppolzer

AW: sql error -199 sqlstate 42601
(in response to Ruediger Kurtz)
IMO, too, the FROM keyword is not allowed in this position.

FWIW, the SQLERRD (5) field in the SQLCA should in this case
show the offset in the SQL statement where DB2 found the syntax error.
If this was run using SPUFI, for example, you divide the value in
SQLERRD(5)
by 72, this way you get the line and the column of the error position in
the SQL input member (ignore the comment lines, of course).

I wrote a tool which replaces SPUFI, and this tool marks the error position
returned by DB2, when echoing the input SQL statement (in case of such
errors),
which is very useful.

Kind regards

Bernd


Am 08.05.2019 um 06:41 schrieb Kurtz, Rüdiger:
>
> Ron,
>
> I can’t say for sure without seeing the full error message but the
> combination of UPDATE … SET … FROM without SELECT somewhere in between
> strikes me as strange.
>
> Rüdiger
>
> Rüdiger Kurtz
> Abteilung Informatik - Betrieb
>
> HUK-COBURG
> Bahnhofsplatz
> 96444 Coburg
> Telefon: 09561 96-44148
> Telefax: 09561 96-44104
> E-Mail: [login to unmask email]
> Internet: www.huk.de
>
> ------------------------------------------------------------------------
> HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter
> Deutschlands a. G. in Coburg
> Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
> Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
> Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
> Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans
> Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas.
> ------------------------------------------------------------------------
> Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte
> Informationen.
> Wenn Sie nicht der richtige Adressat sind oder diese Nachricht
> irrtümlich erhalten haben,
> informieren Sie bitte sofort den Absender und vernichten Sie diese
> Nachricht.
> Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser
> Nachricht ist nicht gestattet.
>
> This information may contain confidential and/or privileged information.
> If you are not the intended recipient (or have received this
> information in error) please notify the
> sender immediately and destroy this information.
> Any unauthorized copying, disclosure or distribution of the material
> in this information is strictly forbidden.
> ------------------------------------------------------------------------
>
> *Von:*Ron Thomas [mailto:[login to unmask email]
> *Gesendet:* Mittwoch, 8. Mai 2019 02:50
> *An:* [login to unmask email]
> *Betreff:* [DB2-L] - sql error -199 sqlstate 42601
>
> Hello . I am executing the following sql to updated the timestamp of
> the latest ITEM_NBR in the table item_log
>
> UPDATE
> B3ITEM.ITEM_log a
> SET
> A.LOG_TS = a.LAST_UPDATE_TS
> FROM
> B3ITEM.ITEM_log b INNER JOIN (
> SELECT
> MAX(s2.log_ts) AS maxts, s2.item_nbr
> FROM
> B3ITEM.ITEM_log s2
> GROUP BY
> s2.item_nbr
> ) k1
>
> ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts
>
> WHERE
> a.item_nbr = k1.item_nbr and a.LAST_CHG_USER_ID = 'IT230X' ;
>
> The above sql is throwing -199 and sqlstate 42601. Could someone let
> me know where the issue is in this sql ?
>
> Regards
>
> Ron T
>
> -----End Original Message-----
>
> -----End Original Message-----

Ron Thomas

RE: AW: sql error -199 sqlstate 42601
(in response to bernd oppolzer)

So any idea how this query can be modified to give the desired result . I need to get the latest row updated for the item_log table for the item_nbrs.

 

I executed the below one to get latest timestamp  for each item_nbr

select k1.item_nbr,k1.log_ts from B3ITEM.ITEM_log k1, (SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr) k2
where k1.item_nbr = k2.item_nbr and k1.log_ts = k2.maxts
order by k1.item_nbr asc ;

but i am not able to update the log_ts with another timestamp of the same table( last_update_ts column name)

 Also, I am pasting the complete error message for the update query

---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FROM. TOKEN
<END-OF-STATEMENT> QUERYNO WHERE SKIP WITH ??( [ CONCAT || / WAS
EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 97 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'00000061' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 13
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 31

Regards

Ron T

Edited By:
Ron Thomas[Organization Members] @ May 08, 2019 - 09:01 AM (America/Eastern)
Ron Thomas[Organization Members] @ May 08, 2019 - 09:16 AM (America/Eastern)

Peter Backlund

sql error -199 sqlstate 42601
(in response to Ron Thomas)
I have added what I believe is needed in red

On 2019-05-08 02:50, Ron Thomas wrote:
UPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = (select a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1

ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts

WHERE
a.item_nbr = k1.item_nbr and a.LAST_CHG_USER_ID = 'IT230X') ;
Best regards,

Peter

--

+--------------------------------+---------------------------------+
| Peter G Backlund | E-mail: [login to unmask email] |
+--------------------------------+---------------------------------+
| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
+--------------------------------+---------------------------------+
| May - September | October - April |
+------------------------------------------------------------------+
| Råsundavägen 94 | Calle Alella 55, Apt 2B |
| S-169 57 SOLNA | 03185 Torrevieja |
| Sweden | Spain |
+--------------------------------+---------------------------------+
| Playing with DB2 since 1981 ... and forever! |
+------------------------------------------------------------------+

Peter Backlund

sql error -199 sqlstate 42601
(in response to Peter Backlund)
I was to quick on the gun and misplaced the ending parenthesis

Peter

On 2019-05-08 15:52, Peter Backlund wrote:
I have added what I believe is needed in red

On 2019-05-08 02:50, Ron Thomas wrote:
UPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = (select a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1

ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts)

WHERE
a.item_nbr = k1.item_nbr and a.LAST_CHG_USER_ID = 'IT230X' ;
Best regards,

Peter

--

+--------------------------------+---------------------------------+
| Peter G Backlund | E-mail: [login to unmask email] |
+--------------------------------+---------------------------------+
| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
+--------------------------------+---------------------------------+
| May - September | October - April |
+------------------------------------------------------------------+
| Råsundavägen 94 | Calle Alella 55, Apt 2B |
| S-169 57 SOLNA | 03185 Torrevieja |
| Sweden | Spain |
+--------------------------------+---------------------------------+
| Playing with DB2 since 1981 ... and forever! |
+-----End Original Message-----+---------------------------------+
| Peter G Backlund | E-mail: [login to unmask email] |
+--------------------------------+---------------------------------+
| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
+--------------------------------+---------------------------------+
| May - September | October - April |
+------------------------------------------------------------------+
| Råsundavägen 94 | Calle Alella 55, Apt 2B |
| S-169 57 SOLNA | 03185 Torrevieja |
| Sweden | Spain |
+--------------------------------+---------------------------------+
| Playing with DB2 since 1981 ... and forever! |
+------------------------------------------------------------------+

Ron Thomas

RE: sql error -199 sqlstate 42601
(in response to Peter Backlund)

Peter - I executed the below query and it is giving -407

UPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = (select a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1 ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts

WHERE
a.item_nbr = k1.item_nbr and a.LAST_update_USERID = 'IT230X') ;

 

i checked the columns  and there is no null values in LAST_UPDATE_TS nor in LOG_TS. Any reason for this one ?

Also, i executed the below query to check and it returned ZERO rows . so why i am not sure it is giving -407

select * from (

select b.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1 ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts ) o where o.LAST_UPDATE_TS is null ;

 

 

Regards

Ron T

 

Edited By:
Ron Thomas[Organization Members] @ May 08, 2019 - 10:45 AM (America/Eastern)

Ron Thomas

sql error -199 sqlstate 42601
(in response to Ron Thomas)
Also , when i executed as follows

UPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = (select a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1

ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts)

WHERE
a.item_nbr = *k1.item_nbr* and a.LAST_CHG_USER_ID = 'IT230X' ;


it says k1.item_nbr is giving error as -206.

Thanks
Ron T

On Wed, May 8, 2019 at 9:24 AM Ron Thomas <[login to unmask email]> wrote:

> Peter - I executed the below query and it is giving -407
>
> UPDATE
> B3ITEM.ITEM_log a
> SET
> A.LOG_TS = (select a.LAST_UPDATE_TS
> FROM
> B3ITEM.ITEM_log b INNER JOIN (
> SELECT
> MAX(s2.log_ts) AS maxts, s2.item_nbr
> FROM
> B3ITEM.ITEM_log s2
> GROUP BY
> s2.item_nbr
> ) k1 ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts
>
> WHERE
> a.item_nbr = k1.item_nbr and a.LAST_update_USERID = 'IT230X') ;
>
>
>
> i checked the columns and there is no null values in LAST_UPDATE_TS nor
> in LOG_TS. Any reason for this one ?
>
>
>
> Regards
>
> Ron T
>
>
>
> -----End Original Message-----
>

Peter Backlund

sql error -199 sqlstate 42601
(in response to Ron Thomas)
This is the one with the misplaced ending parenthesis.
At least you got rid of the syntax error (-199)

Peter

On 2019-05-08 16:24, Ron Thomas wrote:

Peter - I executed the below query and it is giving -407
UPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = (select a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1 ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts
WHERE
a.item_nbr = k1.item_nbr and a.LAST_update_USERID = 'IT230X') ;

i checked the columns  and there is no null values in LAST_UPDATE_TS nor in LOG_TS. Any reason for this one ?

Regards
Ron T

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2
--

+--------------------------------+---------------------------------+
| Peter G Backlund | E-mail: [login to unmask email] |
+--------------------------------+---------------------------------+
| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
+--------------------------------+---------------------------------+
| May - September | October - April |
+------------------------------------------------------------------+
| Råsundavägen 94 | Calle Alella 55, Apt 2B |
| S-169 57 SOLNA | 03185 Torrevieja |
| Sweden | Spain |
+--------------------------------+---------------------------------+
| Playing with DB2 since 1981 ... and forever! |
+------------------------------------------------------------------+

Peter Backlund

sql error -199 sqlstate 42601
(in response to Ron Thomas)
You can not refer to k1 outside the inner select...

Peter

On 2019-05-08 19:23, Ron Thomas wrote:
Also , when i executed as followsUPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = (select a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1

ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts)

WHERE
a.item_nbr = k1.item_nbr and a.LAST_CHG_USER_ID = 'IT230X' ;
it says k1.item_nbr is giving error as -206.
ThanksRon T
On Wed, May 8, 2019 at 9:24 AM Ron Thomas <[login to unmask email]> wrote:

Peter - I executed the below query and it is giving -407
UPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = (select a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1 ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts
WHERE
a.item_nbr = k1.item_nbr and a.LAST_update_USERID = 'IT230X') ;

i checked the columns  and there is no null values in LAST_UPDATE_TS nor in LOG_TS. Any reason for this one ?

Regards
Ron T

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2
--

+--------------------------------+---------------------------------+
| Peter G Backlund | E-mail: [login to unmask email] |
+--------------------------------+---------------------------------+
| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
+--------------------------------+---------------------------------+
| May - September | October - April |
+------------------------------------------------------------------+
| Råsundavägen 94 | Calle Alella 55, Apt 2B |
| S-169 57 SOLNA | 03185 Torrevieja |
| Sweden | Spain |
+--------------------------------+---------------------------------+
| Playing with DB2 since 1981 ... and forever! |
+------------------------------------------------------------------+

Ron Thomas

sql error -199 sqlstate 42601
(in response to Peter Backlund)
so peter how should the query be constructed . I am not getting that .
Could you please let me know how to get it corrected ?

On Wed, May 8, 2019 at 2:34 PM Peter Backlund <[login to unmask email]> wrote:

> You can not refer to k1 outside the inner select...
>
> Peter
>
> On 2019-05-08 19:23, Ron Thomas wrote:
>
> Also , when i executed as follows
>
> UPDATE
> B3ITEM.ITEM_log a
> SET
> A.LOG_TS = (select a.LAST_UPDATE_TS
> FROM
> B3ITEM.ITEM_log b INNER JOIN (
> SELECT
> MAX(s2.log_ts) AS maxts, s2.item_nbr
> FROM
> B3ITEM.ITEM_log s2
> GROUP BY
> s2.item_nbr
> ) k1
>
> ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts)
>
> WHERE
> a.item_nbr = *k1.item_nbr* and a.LAST_CHG_USER_ID = 'IT230X' ;
>
>
> it says k1.item_nbr is giving error as -206.
>
> Thanks
> Ron T
>
> On Wed, May 8, 2019 at 9:24 AM Ron Thomas <[login to unmask email]> wrote:
>
>> Peter - I executed the below query and it is giving -407
>>
>> UPDATE
>> B3ITEM.ITEM_log a
>> SET
>> A.LOG_TS = (select a.LAST_UPDATE_TS
>> FROM
>> B3ITEM.ITEM_log b INNER JOIN (
>> SELECT
>> MAX(s2.log_ts) AS maxts, s2.item_nbr
>> FROM
>> B3ITEM.ITEM_log s2
>> GROUP BY
>> s2.item_nbr
>> ) k1 ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts
>>
>> WHERE
>> a.item_nbr = k1.item_nbr and a.LAST_update_USERID = 'IT230X') ;
>>
>>
>>
>> i checked the columns and there is no null values in LAST_UPDATE_TS nor
>> in LOG_TS. Any reason for this one ?
>>
>>
>>
>> Regards
>>
>> Ron T
>>
>>
>>
>> -----End Original Message-----
>>
>
> --
>
> +--------------------------------+---------------------------------+
> | Peter G Backlund | E-mail: [login to unmask email] |
> +--------------------------------+---------------------------------+
> | Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
> +--------------------------------+---------------------------------+
> | May - September | October - April |
> +------------------------------------------------------------------+
> | Råsundavägen 94 | Calle Alella 55, Apt 2B |
> | S-169 57 SOLNA | 03185 Torrevieja |
> | Sweden | Spain |
> +--------------------------------+---------------------------------+
> | Playing with DB2 since 1981 ... and forever! |
> +------------------------------------------------------------------+
>
>
> -----End Original Message-----
>
>

bernd oppolzer

AW: sql error -199 sqlstate 42601
(in response to Ron Thomas)
Don't know if this is what you want to do:


UPDATE B3ITEM.ITEM_log a
   SET A.LOG_TS = a.LAST_UPDATE_TS
 WHERE A.LOG_TS =
       (SELECT MAX (LOG_TS)
          FROM B3ITEM.ITEM_log b
         WHERE b.item_nbr = a.item_nbr)
   and a.LAST_CHG_USER_ID = 'IT230X';

This UPDATE should for every item_nbr update the row with the highest
LOG_TS (I hope that there is only one such row) if the LAST_CHG_USER_ID
is 'IT230X'. It sets the LOG_TS to the value of LAST_UPDATE_TS.

No join needed; the condition is enforced by the correlated subquery.

Others may look at this to find logic or syntactic errors in it;
and you should check if that really is what you want.

HTH, kind regards

Bernd



Am 08.05.2019 um 14:50 schrieb Ron Thomas:
>
> So any idea how this query can be modified to give the desired result
> . I need to get the latest row updated for the item_log table for the
> item_nbrs.
>
> Regards
>
> Ron T
>
>
> -----End Original Message-----

Walter Jani&#223;en

AW: sql error -199 sqlstate 42601
(in response to Ron Thomas)
Hi

AFAIK, it is not allowed to qualify the column mentioned in a SET-clause:

UPDATE
B3ITEM.ITEM_log a
SET
LOG_TS = (select a.LAST_UPDATE_TS
FROM


Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Ron Thomas <[login to unmask email]>
Gesendet: Mittwoch, 8. Mai 2019 22:11
An: [login to unmask email]
Betreff: [DB2-L] - RE: sql error -199 sqlstate 42601

so peter how should the query be constructed . I am not getting that . Could you please let me know how to get it corrected ?

On Wed, May 8, 2019 at 2:34 PM Peter Backlund <[login to unmask email]<mailto:[login to unmask email]>> wrote:
You can not refer to k1 outside the inner select...

Peter
On 2019-05-08 19:23, Ron Thomas wrote:
Also , when i executed as follows
UPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = (select a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1

ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts)

WHERE
a.item_nbr = k1.item_nbr and a.LAST_CHG_USER_ID = 'IT230X' ;

it says k1.item_nbr is giving error as -206.

Thanks
Ron T

On Wed, May 8, 2019 at 9:24 AM Ron Thomas <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Peter - I executed the below query and it is giving -407

UPDATE
B3ITEM.ITEM_log a
SET
A.LOG_TS = (select a.LAST_UPDATE_TS
FROM
B3ITEM.ITEM_log b INNER JOIN (
SELECT
MAX(s2.log_ts) AS maxts, s2.item_nbr
FROM
B3ITEM.ITEM_log s2
GROUP BY
s2.item_nbr
) k1 ON b.item_nbr = k1.item_nbr and k1.maxts = b.log_ts

WHERE
a.item_nbr = k1.item_nbr and a.LAST_update_USERID = 'IT230X') ;



i checked the columns and there is no null values in LAST_UPDATE_TS nor in LOG_TS. Any reason for this one ?



Regards

Ron T



-----End Original Message-----



--



+--------------------------------+---------------------------------+

| Peter G Backlund | E-mail: [login to unmask email]<mailto:[login to unmask email]> |

+--------------------------------+---------------------------------+

| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |

+--------------------------------+---------------------------------+

| May - September | October - April |

+------------------------------------------------------------------+

| Råsundavägen 94 | Calle Alella 55, Apt 2B |

| S-169 57 SOLNA | 03185 Torrevieja |

| Sweden | Spain |

+--------------------------------+---------------------------------+

| Playing with DB2 since 1981 ... and forever! |

+------------------------------------------------------------------+

-----End Original Message-----
Attachments

  • image001.png (2.6k)

bernd oppolzer

AW: sql error -199 sqlstate 42601
(in response to bernd oppolzer)
Another improvement (?):

first of all, I would like to omit the unneeded qualifiers, so it becomes:

UPDATE B3ITEM.ITEM_log A
   SET LOG_TS = LAST_UPDATE_TS
 WHERE LOG_TS =
       (SELECT MAX (LOG_TS)
          FROM B3ITEM.ITEM_log
         WHERE item_nbr = A.item_nbr)
   and LAST_CHG_USER_ID = 'IT230X';

only the inter block reference A is needed in one place.

and second:

to first check out, which rows are to be updated, I would convert this
UPDATE to a SELECT:

SELECT ITEM_NBR, LOG_TS, LAST_UPDATE_TS, LAST_CHG_USER_ID
  FROM UPDATE B3ITEM.ITEM_log A
 WHERE LOG_TS =
       (SELECT MAX (LOG_TS)
          FROM B3ITEM.ITEM_log
         WHERE item_nbr = A.item_nbr)
   and LAST_CHG_USER_ID = 'IT230X';

but remember: I still have some worry about updating the LOG_TS in this
way,
because I could imagine that the UPDATED record qualifies once again and
is UPDATED a second time !!! (Halloween effect)

This, of course, would depend heavily on available indexes and if they are
defined ASC or DESC, and on the access path DB2 chooses. Although the risk
may be minimal, I would try to avoid it.

Kind regards

Bernd



Am 09.05.2019 um 00:21 schrieb Bernd Oppolzer:
>
> Don't know if this is what you want to do:
>
>
> UPDATE B3ITEM.ITEM_log a
>    SET A.LOG_TS = a.LAST_UPDATE_TS
>  WHERE A.LOG_TS =
>        (SELECT MAX (LOG_TS)
>           FROM B3ITEM.ITEM_log b
>          WHERE b.item_nbr = a.item_nbr)
>    and a.LAST_CHG_USER_ID = 'IT230X';
>
> This UPDATE should for every item_nbr update the row with the highest
> LOG_TS (I hope that there is only one such row) if the LAST_CHG_USER_ID
> is 'IT230X'. It sets the LOG_TS to the value of LAST_UPDATE_TS.
>
> No join needed; the condition is enforced by the correlated subquery.
>
> Others may look at this to find logic or syntactic errors in it;
> and you should check if that really is what you want.
>
> HTH, kind regards
>
> Bernd
>
>
>
> Am 08.05.2019 um 14:50 schrieb Ron Thomas:
>>
>> So any idea how this query can be modified to give the desired result
>> . I need to get the latest row updated for the item_log table for the
>> item_nbrs.
>>
>> Regards
>>
>> Ron T
>>
>>
>> -----End Original Message-----
>
> -----End Original Message-----

Ron Thomas

RE: AW: sql error -199 sqlstate 42601
(in response to bernd oppolzer)

Thanks Bernd for your inputs. I was able to fix the query . 

Regards

Ron T

Peter Vanroose

RE: sql error -199 sqlstate 42601
(in response to bernd oppolzer)

In this case, with a single UPDATE statement, the Halloween effect would never happen since the Db2 optimizer will avoid using the "dangerous" index in the access path.

It's only if you (1) declare a cursor in an application program, which (2) you don't declare "for update of log_ts", and then (3) do an out-of-cursor update inside the cursor loop, that the same row could pass by multiple times.

In Reply to bernd oppolzer:

[...] I still have some worry about updating the LOG_TS in this way, because I could imagine that the UPDATED record qualifies once again and is UPDATED a second time !!! (Halloween effect)
This, of course, would depend heavily on available indexes and if they are defined ASC or DESC, and on the access path DB2 chooses. Although the risk may be minimal, I would try to avoid it. [...]

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Michael Hannan

RE: sql error -199 sqlstate 42601
(in response to Peter Vanroose)

Very interesting topic. Only slack programming should experience the halloween bug, mostly.

My understanding of the Halloween bug is any index scan where an entry is updated and reinserted ahead of the scan by your own Update statement, or similarly for a TS Scan where a row was moved, e.g. partition key updated, again to a partition ahead of the scan, and potentially obtained twice. 

I believe that if FOR UPDATE OF COL_TIMESTAMP is specified on a Cursor, and DB2 needs to use an index including that column, then it should choose a List Prefetch access path, that prevents updated index entries from being revisited (index entries and rids collected up front before Update starts). I recall a release where I saw many switches to List Prefetch for Updates (Update Cursors and stand alone Updates). Without that clause, don't expect DB2 to help protect you, unless it  sees your local Update current of Cursor maybe. 

Not aware if you are  protected in anyway for a Tablespace scan against hitting the same row twice, when the partition key update moved it. So that is an area for special care.

Also suppose some other thread does the Update to the sequencing Timestamp, then you you could get same entry twice (or miss it completely), without having done the update yourself, simply because the other query committed its update very fast. This is not known as The Halloween Effect I guess. For TS Scan, at one time IBM tried to protect us by locking the from and to partitions and all in between, which was too nasty to bear, specially given there was no such protection for entries in an index moved by another thread process.

Try to include extra predicates that might discourage picking up data just being updated a second time, however if you are just listing data and not doing an update yourself, not a big deal. Hopefully Repeatable Read locking is rarely required.

In Reply to Peter Vanroose:

In this case, with a single UPDATE statement, the Halloween effect would never happen since the Db2 optimizer will avoid using the "dangerous" index in the access path.

It's only if you (1) declare a cursor in an application program, which (2) you don't declare "for update of log_ts", and then (3) do an out-of-cursor update inside the cursor loop, that the same row could pass by multiple times.


Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Peter Vanroose

RE: sql error -199 sqlstate 42601
(in response to Michael Hannan)

In Reply to Michael Hannan:

[...] Also suppose some other thread does the Update to the sequencing Timestamp, then you you could get same entry twice (or miss it completely), without having done the update yourself, simply because the other query committed its update very fast. This is not known as The Halloween Effect I guess.

No, indeed; this is known as a Phantom Read. Cursor Stability cannot protect against it; only Repeatable Read can.
But you may only observe it when you read data, not when updating.

When both parties update, they cannot hold a lock on the same entry (since that lock will at least be a for-update lock). So a locked entry could never have been updated by an other party (hence you can never see it again in the same update process).

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/