weirdness with temp tables

HPUX 11.31 on HP BL870c IDS 12.10.FC5 The script hasn't been changed recently (3-4 payrolls ran since last change with no problem), no database changes to tables used in the script and no changed to IDS. We have a weird situation here that is baffling us. There is a HR script that generates a number of temp tables to calculate OT and weekly hours worked. Been running for a number of years without problems. Ran ok about a week ago, but on Monday it did not properly run. Is there a way to see what the database thinks the fields are in a temp table? I ask, as we are wondering if somehow the fields from the different database tables that make up the temp tables somehow got the field definitions changed. Like maybe the ID integer field got somehow changed to a character filed. What happened was, it duplicated information as if one of the "WHERE" clauses didn't work on the last statement. We got the script working by adding a cast (integer) on the ID field of the temp table. The ID field in the database table is integer and the bal_prd (pay period) and cal_wk are characters fields. The way the script works is it starts by loading a temp table with all the timesheet records for a given bal_prd (pay period) and then through a number of steps using other tables to categorize the hours worked (normal or OT) sums up the hours for both. The last step sum up the hours per week and inserts the data into the payroll record to be processed and paid. In the example below instead of 3 records inserted, one for each week we got three records for each week (so go 9 records). The selection is on ID, bal_prd and cal_wk that match the temp table and the original timesheet table. ID bal_prd cal_wk hrs 12345 STU123 15 10.000 12345 STU123 16 8.000 12345 STU123 17 10.000 I'm sure I'm explaining this problem poorly. John David Adamski, Sr. Network Specialist Graceland University, 1 University Place, Lamoni, IA 50140 adamski@graceland.edu 641.784.5267

John: This sounds suspiciously like a known bug that there may be a recent fix for. Open a PMR. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 4, 2016 at 10:36 AM, John Adamski (Work Account) < adamski@graceland.edu> wrote: > HPUX 11.31 on HP BL870c > IDS 12.10.FC5 > > The script hasn't been changed recently (3-4 payrolls ran since last change > with no problem), no database changes to tables used in the script and no > changed to IDS. > > We have a weird situation here that is baffling us. There is a HR script > that > generates a number of temp tables to calculate OT and weekly hours worked. > Been running for a number of years without problems. Ran ok about a week > ago, > but on Monday it did not properly run. > > Is there a way to see what the database thinks the fields are in a temp > table? > > I ask, as we are wondering if somehow the fields from the different > database > tables that make up the temp tables somehow got the field definitions > changed. > Like maybe the ID integer field got somehow changed to a character filed. > > What happened was, it duplicated information as if one of the "WHERE" > clauses > didn't work on the last statement. We got the script working by adding a > cast > (integer) on the ID field of the temp table. The ID field in the database > table is integer and the bal_prd (pay period) and cal_wk are characters > fields. > > The way the script works is it starts by loading a temp table with all the > timesheet records for a given bal_prd (pay period) and then through a > number > of steps using other tables to categorize the hours worked (normal or OT) > sums > up the hours for both. The last step sum up the hours per week and inserts > the > data into the payroll record to be processed and paid. > > In the example below instead of 3 records inserted, one for each week we > got > three records for each week (so go 9 records). The selection is on ID, > bal_prd > and cal_wk that match the temp table and the original timesheet table. > > ID bal_prd cal_wk hrs > 12345 STU123 15 10.000 > 12345 STU123 16 8.000 > 12345 STU123 17 10.000 > > I'm sure I'm explaining this problem poorly. > > John David Adamski, Sr. Network Specialist > Graceland University, 1 University Place, Lamoni, IA 50140 > adamski@graceland.edu > 641.784.5267 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93403f950afab053206110d

I would need more debug data... The only thing I can say is that Informix is rather smart when trying to compare numeric and char fields. Within this "intelligence" it really optimizes the queries if that don't risk having a wrong result set. So, what I'm trying to say is that if your data changes (length of fields for example) it may change the query plans. But it should not change the result set. Regards. On Wed, May 4, 2016 at 3:36 PM, John Adamski (Work Account) < adamski@graceland.edu> wrote: > HPUX 11.31 on HP BL870c > IDS 12.10.FC5 > > The script hasn't been changed recently (3-4 payrolls ran since last change > with no problem), no database changes to tables used in the script and no > changed to IDS. > > We have a weird situation here that is baffling us. There is a HR script > that > generates a number of temp tables to calculate OT and weekly hours worked. > Been running for a number of years without problems. Ran ok about a week > ago, > but on Monday it did not properly run. > > Is there a way to see what the database thinks the fields are in a temp > table? > > I ask, as we are wondering if somehow the fields from the different > database > tables that make up the temp tables somehow got the field definitions > changed. > Like maybe the ID integer field got somehow changed to a character filed. > > What happened was, it duplicated information as if one of the "WHERE" > clauses > didn't work on the last statement. We got the script working by adding a > cast > (integer) on the ID field of the temp table. The ID field in the database > table is integer and the bal_prd (pay period) and cal_wk are characters > fields. > > The way the script works is it starts by loading a temp table with all the > timesheet records for a given bal_prd (pay period) and then through a > number > of steps using other tables to categorize the hours worked (normal or OT) > sums > up the hours for both. The last step sum up the hours per week and inserts > the > data into the payroll record to be processed and paid. > > In the example below instead of 3 records inserted, one for each week we > got > three records for each week (so go 9 records). The selection is on ID, > bal_prd > and cal_wk that match the temp table and the original timesheet table. > > ID bal_prd cal_wk hrs > 12345 STU123 15 10.000 > 12345 STU123 16 8.000 > 12345 STU123 17 10.000 > > I'm sure I'm explaining this problem poorly. > > John David Adamski, Sr. Network Specialist > Graceland University, 1 University Place, Lamoni, IA 50140 > adamski@graceland.edu > 641.784.5267 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e013c64129723a20532064d1f

Thanks, due to licensing we have to go through our erp provide for anything dealing with IBM. :-( John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, May 04, 2016 11:11 AM To: ids@iiug.org Subject: Re: weirdness with temp tables [37081] John: This sounds suspiciously like a known bug that there may be a recent fix for. Open a PMR. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 4, 2016 at 10:36 AM, John Adamski (Work Account) < adamski@graceland.edu> wrote: > HPUX 11.31 on HP BL870c > IDS 12.10.FC5 > > The script hasn't been changed recently (3-4 payrolls ran since last > change with no problem), no database changes to tables used in the > script and no changed to IDS. > > We have a weird situation here that is baffling us. There is a HR > script that generates a number of temp tables to calculate OT and > weekly hours worked. > Been running for a number of years without problems. Ran ok about a > week ago, but on Monday it did not properly run. > > Is there a way to see what the database thinks the fields are in a > temp table? > > I ask, as we are wondering if somehow the fields from the different > database tables that make up the temp tables somehow got the field > definitions changed. > Like maybe the ID integer field got somehow changed to a character filed. > > What happened was, it duplicated information as if one of the "WHERE" > clauses > didn't work on the last statement. We got the script working by adding > a cast > (integer) on the ID field of the temp table. The ID field in the > database table is integer and the bal_prd (pay period) and cal_wk are > characters fields. > > The way the script works is it starts by loading a temp table with all > the timesheet records for a given bal_prd (pay period) and then > through a number of steps using other tables to categorize the hours > worked (normal or OT) sums up the hours for both. The last step sum up > the hours per week and inserts the data into the payroll record to be > processed and paid. > > In the example below instead of 3 records inserted, one for each week > we got three records for each week (so go 9 records). The selection is > on ID, bal_prd and cal_wk that match the temp table and the original > timesheet table. > > ID bal_prd cal_wk hrs > 12345 STU123 15 10.000 > 12345 STU123 16 8.000 > 12345 STU123 17 10.000 > > I'm sure I'm explaining this problem poorly. > > John David Adamski, Sr. Network Specialist Graceland University, 1 > University Place, Lamoni, IA 50140 adamski@graceland.edu > 641.784.5267 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93403f950afab053206110d ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

We agree shouldn't change the resulting set and neither should the 'fix' made a difference. Art thinks this might be a known bug so I'm going to pursue that avenue. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, May 04, 2016 11:19 AM To: ids@iiug.org Subject: Re: weirdness with temp tables [37082] I would need more debug data... The only thing I can say is that Informix is rather smart when trying to compare numeric and char fields. Within this "intelligence" it really optimizes the queries if that don't risk having a wrong result set. So, what I'm trying to say is that if your data changes (length of fields for example) it may change the query plans. But it should not change the result set. Regards. On Wed, May 4, 2016 at 3:36 PM, John Adamski (Work Account) < adamski@graceland.edu> wrote: > HPUX 11.31 on HP BL870c > IDS 12.10.FC5 > > The script hasn't been changed recently (3-4 payrolls ran since last > change with no problem), no database changes to tables used in the > script and no changed to IDS. > > We have a weird situation here that is baffling us. There is a HR > script that generates a number of temp tables to calculate OT and > weekly hours worked. > Been running for a number of years without problems. Ran ok about a > week ago, but on Monday it did not properly run. > > Is there a way to see what the database thinks the fields are in a > temp table? > > I ask, as we are wondering if somehow the fields from the different > database tables that make up the temp tables somehow got the field > definitions changed. > Like maybe the ID integer field got somehow changed to a character filed. > > What happened was, it duplicated information as if one of the "WHERE" > clauses > didn't work on the last statement. We got the script working by adding > a cast > (integer) on the ID field of the temp table. The ID field in the > database table is integer and the bal_prd (pay period) and cal_wk are > characters fields. > > The way the script works is it starts by loading a temp table with all > the timesheet records for a given bal_prd (pay period) and then > through a number of steps using other tables to categorize the hours > worked (normal or OT) sums up the hours for both. The last step sum up > the hours per week and inserts the data into the payroll record to be > processed and paid. > > In the example below instead of 3 records inserted, one for each week > we got three records for each week (so go 9 records). The selection is > on ID, bal_prd and cal_wk that match the temp table and the original > timesheet table. > > ID bal_prd cal_wk hrs > 12345 STU123 15 10.000 > 12345 STU123 16 8.000 > 12345 STU123 17 10.000 > > I'm sure I'm explaining this problem poorly. > > John David Adamski, Sr. Network Specialist Graceland University, 1 > University Place, Lamoni, IA 50140 adamski@graceland.edu > 641.784.5267 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e013c64129723a20532064d1f ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I was speaking to John Miller about this one. An additional question: - How was the temp table created? Was it created by a CREATE TEMP TABLE statement with explicit types or by an implicit create perhaps from a SELECT .... INTO TEMP;? If the latter, then it might be that the inputs included a non-numeric character in the id field in the first selected row. That might have caused the temp table to have the id column as CHAR instead of INT. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 4, 2016 at 2:15 PM, John Adamski (Work Account) < adamski@graceland.edu> wrote: > We agree shouldn't change the resulting set and neither should the 'fix' > made > a difference. Art thinks this might be a known bug so I'm going to pursue > that > avenue. > > John > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando > Nunes > Sent: Wednesday, May 04, 2016 11:19 AM > To: ids@iiug.org > Subject: Re: weirdness with temp tables [37082] > > I would need more debug data... > The only thing I can say is that Informix is rather smart when trying to > compare numeric and char fields. Within this "intelligence" it really > optimizes the queries if that don't risk having a wrong result set. > > So, what I'm trying to say is that if your data changes (length of fields > for > example) it may change the query plans. But it should not change the result > set. > > Regards. > > On Wed, May 4, 2016 at 3:36 PM, John Adamski (Work Account) < > adamski@graceland.edu> wrote: > > > HPUX 11.31 on HP BL870c > > IDS 12.10.FC5 > > > > The script hasn't been changed recently (3-4 payrolls ran since last > > change with no problem), no database changes to tables used in the > > script and no changed to IDS. > > > > We have a weird situation here that is baffling us. There is a HR > > script that generates a number of temp tables to calculate OT and > > weekly hours worked. > > Been running for a number of years without problems. Ran ok about a > > week ago, but on Monday it did not properly run. > > > > Is there a way to see what the database thinks the fields are in a > > temp table? > > > > I ask, as we are wondering if somehow the fields from the different > > database tables that make up the temp tables somehow got the field > > definitions changed. > > Like maybe the ID integer field got somehow changed to a character filed. > > > > What happened was, it duplicated information as if one of the "WHERE" > > clauses > > didn't work on the last statement. We got the script working by adding > > a cast > > (integer) on the ID field of the temp table. The ID field in the > > database table is integer and the bal_prd (pay period) and cal_wk are > > characters fields. > > > > The way the script works is it starts by loading a temp table with all > > the timesheet records for a given bal_prd (pay period) and then > > through a number of steps using other tables to categorize the hours > > worked (normal or OT) sums up the hours for both. The last step sum up > > the hours per week and inserts the data into the payroll record to be > > processed and paid. > > > > In the example below instead of 3 records inserted, one for each week > > we got three records for each week (so go 9 records). The selection is > > on ID, bal_prd and cal_wk that match the temp table and the original > > timesheet table. > > > > ID bal_prd cal_wk hrs > > 12345 STU123 15 10.000 > > 12345 STU123 16 8.000 > > 12345 STU123 17 10.000 > > > > I'm sure I'm explaining this problem poorly. > > > > John David Adamski, Sr. Network Specialist Graceland University, 1 > > University Place, Lamoni, IA 50140 adamski@graceland.edu > > 641.784.5267 > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --089e013c64129723a20532064d1f > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bfeab621cb602053209ef65