Re: Could not do a physical-order read to fetch next row.

rakesh_sa@yahoo.com wrote: > Hi All, > > I am geetting an Error as "Caused by: java.sql.SQLException: Could not > do a physical-order read to fetch next row." . Database used is IDS 9.4 > . One table is getting locked . Even after changing the LOCK MODE to > ROW the problem is persisting. What can be the solution for the same. > > Please reply to me asap. > > Thanks > Rakesh. > Besides the problems mentioned, in this error "physical-order read' indicates that the query is performing a table scan. Either you are missing an index on the key you are querying on or the table's data distributions are out-of-date and the optimizer is generating a poor query plan (unless this table is VERY small or the query will be processing most rows in the table). You need to see if an index needs to be added and to run UPDATE STATISTICS according to the guidelines in the Performance Guide and/or John Miller III's paper: (http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html) You can also get and use my dostats utility which implements these recommendations. Dostats is part of the package utils2_ak available for download from the IIUG Software Repository (www.iiug.org/software). Art S. Kagel

Thanks a lot for your suggession .I need some more advise & help from you on writing complex queries in informix . I had referred some pdf docs which explains sql statements which are not very complex .For ex , I am looking some sql which can talk of how to convert numbers to string say if i have value 5 in a column & based on the value present i need to display "FIVE" as an output in the query . Once again thanks a lot. Rakesh. Art S. Kagel wrote: > rakesh_sa@yahoo.com wrote: > > Hi All, > > > > I am geetting an Error as "Caused by: java.sql.SQLException: Could not > > do a physical-order read to fetch next row." . Database used is IDS 9.4 > > . One table is getting locked . Even after changing the LOCK MODE to > > ROW the problem is persisting. What can be the solution for the same. > > > > Please reply to me asap. > > > > Thanks > > Rakesh. > > > Besides the problems mentioned, in this error "physical-order read' > indicates that the query is performing a table scan. Either you are missing > an index on the key you are querying on or the table's data distributions > are out-of-date and the optimizer is generating a poor query plan (unless > this table is VERY small or the query will be processing most rows in the > table). You need to see if an index needs to be added and to run UPDATE > STATISTICS according to the guidelines in the Performance Guide and/or John > Miller III's paper: > (http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html) > > You can also get and use my dostats utility which implements these > recommendations. Dostats is part of the package utils2_ak available for > download from the IIUG Software Repository (www.iiug.org/software). > > Art S. Kagel

rakesh_sa@yahoo.com wrote: > Thanks a lot for your suggession .I need some more advise & help from > you on writing complex > queries in informix . I had referred some pdf docs which explains sql > statements which are not > very complex .For ex , I am looking some sql which can talk of how to > convert numbers to string say if i have value 5 in a column & based on > the value present i need to display "FIVE" as an output in the query . > > Once again thanks a lot. > > Rakesh. <SNIP> That's not complex SQL it's data conversion. You have several options. If the range of values is rather limited, you can use a CASE clause in the SELECT: select char_col, CASE numeric_col WHEN 1 THEN 'ONE' WHEN 2 THEN 'TWO' WHEN 3 THEN 'THREE'... If the range is a bit larger you can load the transations in to a table: create table number_name_lookup( number integer, name varchar(3,20) ); insert into number_name_lookup values (1, 'ONE'); insert into number_name_lookup values (2, 'TWO');... If a general capability is needed, then you'll have to write a C or Java UDR (User Defined Routine) to do the translation, link it into a shared library (of it's written in C), install it in the engine, and then call the func from the SELECT: select char_col, number_to_name( numeric_col ).... Art S. Kagel