7.x to 9.x Performance issue in the extreme

[Tom Lowrie]

Greetings,

The problem: I run an identical program on Server A and Server B. On
Server A the program runs in 12 seconds. On Server B it takes 1.5
hours.

Server A: IBM RS/6000 H80, AIX 4.3.3.0.11, IBM IDS 7.31.UC7

Server B: IBM RS/6000 S80, AIX 4.3.3.0.9, IBM IDS 9.30.UC2

The only difference in the hardware on these 2 servers is Server A has
6 Gigabytes of RAM while Server B has 4. The SSA Disk subsystems are
identical.

Both servers use the 4J's "front end", installed and compiled on each
server in accordance with the 4J's installation guidelines.

Both servers are dedicated to running the IDS and the Application
software only. The application software is identical on both servers.
The application software resides on the same server as the IDS. Both
servers are pretty much running at the same load level – not stressed
at all. Both appear to have plenty of available memory and plenty of
available CPU time. There is no I/O issue on either server. In short,
plenty of hardware for the task at hand.

The data in the DB is identical on both servers. The program that
generates the update statistics SQL is identical on both servers. The
indexes are identical on both servers. The total size of the database
after import is less than 2.5 Gig. The tables being accessed for this
report are all under 120 Meg.

The onconfig files are identical on both servers except for:

Variable: Server A: Sever B:
SHMVIRTSIZE 786432 524288
SHMADD 262144 393216
STACKSIZE 32 64

The program is a YTD Budget Report that takes a few parameters then
goes out to the database to collect the data and generate the report.
Nothing fancy. Nothing spectacular. Just your normal, everyday
accounting type report.

The speed on system B in all other areas is excellent. Normal "Finds"
against the largest tables, moving between screens,
adding/deleting/changing field data, etc all runs fine.

Given this info, where do I look for the problem?

[Fernando Nunes]

Tom Lowrie wrote:

> Given this info, where do I look for the problem?

If you have access to the source code, please make some logging in order to see which sections take more time to complete.
You can also try to monitor the session...

Regards

[Obnoxio The Clown]

Tom Lowrie wrote:

> Greetings,
>
> The problem: I run an identical program on Server A and Server B. On
> Server A the program runs in 12 seconds. On Server B it takes 1.5
> hours.
>
> Server A: IBM RS/6000 H80, AIX 4.3.3.0.11, IBM IDS 7.31.UC7
>
> Server B: IBM RS/6000 S80, AIX 4.3.3.0.9, IBM IDS 9.30.UC2
>
> The only difference in the hardware on these 2 servers is Server A has
> 6 Gigabytes of RAM while Server B has 4. The SSA Disk subsystems are
> identical.
>
> Both servers use the 4J's "front end", installed and compiled on each
> server in accordance with the 4J's installation guidelines.
>
> Both servers are dedicated to running the IDS and the Application
> software only. The application software is identical on both servers.
> The application software resides on the same server as the IDS. Both
> servers are pretty much running at the same load level – not stressed
> at all. Both appear to have plenty of available memory and plenty of
> available CPU time. There is no I/O issue on either server. In short,
> plenty of hardware for the task at hand.
>
> The data in the DB is identical on both servers. The program that
> generates the update statistics SQL is identical on both servers. The
> indexes are identical on both servers. The total size of the database
> after import is less than 2.5 Gig. The tables being accessed for this
> report are all under 120 Meg.
>
> The onconfig files are identical on both servers except for:
>
> Variable: Server A: Sever B:
> SHMVIRTSIZE 786432 524288
> SHMADD 262144 393216
> STACKSIZE 32 64
>
> The program is a YTD Budget Report that takes a few parameters then
> goes out to the database to collect the data and generate the report.
> Nothing fancy. Nothing spectacular. Just your normal, everyday
> accounting type report.
>
> The speed on system B in all other areas is excellent. Normal "Finds"
> against the largest tables, moving between screens,
> adding/deleting/changing field data, etc all runs fine.
>
> Given this info, where do I look for the problem?

UPDATE STATISTICS?

--
Ciao,
The Obnoxious One

"Ogni uomo mi guarda come se fossi una testa di cazzo"

[Bill Dare]

Try running onstat -g ses SESSION_ID for the session that is taking so long.
Assuming you are correct about the hardware resources being adequate, I'd
look for this session or the application process that started the session to
be waiting on some condition. Maybe a lock or some other shared resource.

Regards,
Bill Dare

> -----Original Message-----
> From: [email]tlowrie@munis.com[/email] [SMTP:tlowrie@munis.com]
> Sent: Thursday, October 02, 2003 11:37 AM
> To: [email]informix-list@iiug.org[/email]
> Subject: 7.x to 9.x Performance issue in the extreme
>
> Greetings,
>
> The problem: I run an identical program on Server A and Server B. On
> Server A the program runs in 12 seconds. On Server B it takes 1.5
> hours.
>
> Server A: IBM RS/6000 H80, AIX 4.3.3.0.11, IBM IDS 7.31.UC7
>
> Server B: IBM RS/6000 S80, AIX 4.3.3.0.9, IBM IDS 9.30.UC2
>
> The only difference in the hardware on these 2 servers is Server A has
> 6 Gigabytes of RAM while Server B has 4. The SSA Disk subsystems are
> identical.
>
> Both servers use the 4J's "front end", installed and compiled on each
> server in accordance with the 4J's installation guidelines.
>
> Both servers are dedicated to running the IDS and the Application
> software only. The application software is identical on both servers.
> The application software resides on the same server as the IDS. Both
> servers are pretty much running at the same load level - not stressed
> at all. Both appear to have plenty of available memory and plenty of
> available CPU time. There is no I/O issue on either server. In short,
> plenty of hardware for the task at hand.
>
> The data in the DB is identical on both servers. The program that
> generates the update statistics SQL is identical on both servers. The
> indexes are identical on both servers. The total size of the database
> after import is less than 2.5 Gig. The tables being accessed for this
> report are all under 120 Meg.
>
> The onconfig files are identical on both servers except for:
>
> Variable: Server A: Sever B:
> SHMVIRTSIZE 786432 524288
> SHMADD 262144 393216
> STACKSIZE 32 64
>
> The program is a YTD Budget Report that takes a few parameters then
> goes out to the database to collect the data and generate the report.
> Nothing fancy. Nothing spectacular. Just your normal, everyday
> accounting type report.
>
> The speed on system B in all other areas is excellent. Normal "Finds"
> against the largest tables, moving between screens,
> adding/deleting/changing field data, etc all runs fine.
>
> Given this info, where do I look for the problem?

sending to informix-list

[Jeff]

Have you tried running the query against both servers with "set explain on".
--
Jeff
jlar310 at yahoo


"Tom Lowrie" <tlowrie@munis.com> wrote in message news:e91c92f1.0310020736.6b8acebe@posting.google.c om...

> Greetings,
>
> The problem: I run an identical program on Server A and Server B. On
> Server A the program runs in 12 seconds. On Server B it takes 1.5
> hours.
>
> Server A: IBM RS/6000 H80, AIX 4.3.3.0.11, IBM IDS 7.31.UC7
>
> Server B: IBM RS/6000 S80, AIX 4.3.3.0.9, IBM IDS 9.30.UC2
>
> The only difference in the hardware on these 2 servers is Server A has
> 6 Gigabytes of RAM while Server B has 4. The SSA Disk subsystems are
> identical.
>
> Both servers use the 4J's "front end", installed and compiled on each
> server in accordance with the 4J's installation guidelines.
>
> Both servers are dedicated to running the IDS and the Application
> software only. The application software is identical on both servers.
> The application software resides on the same server as the IDS. Both
> servers are pretty much running at the same load level - not stressed
> at all. Both appear to have plenty of available memory and plenty of
> available CPU time. There is no I/O issue on either server. In short,
> plenty of hardware for the task at hand.
>
> The data in the DB is identical on both servers. The program that
> generates the update statistics SQL is identical on both servers. The
> indexes are identical on both servers. The total size of the database
> after import is less than 2.5 Gig. The tables being accessed for this
> report are all under 120 Meg.
>
> The onconfig files are identical on both servers except for:
>
> Variable: Server A: Sever B:
> SHMVIRTSIZE 786432 524288
> SHMADD 262144 393216
> STACKSIZE 32 64
>
> The program is a YTD Budget Report that takes a few parameters then
> goes out to the database to collect the data and generate the report.
> Nothing fancy. Nothing spectacular. Just your normal, everyday
> accounting type report.
>
> The speed on system B in all other areas is excellent. Normal "Finds"
> against the largest tables, moving between screens,
> adding/deleting/changing field data, etc all runs fine.
>
> Given this info, where do I look for the problem?

[Mark Denham]

----- Original Message -----
From: "Tom Lowrie" <tlowrie@munis.com>
To: <informix-list@iiug.org>
Sent: Thursday, October 02, 2003 11:36
Subject: 7.x to 9.x Performance issue in the extreme

> Greetings,
>
> The problem: I run an identical program on Server A and Server B. On
> Server A the program runs in 12 seconds. On Server B it takes 1.5
> hours.
>
> Server A: IBM RS/6000 H80, AIX 4.3.3.0.11, IBM IDS 7.31.UC7
>
> Server B: IBM RS/6000 S80, AIX 4.3.3.0.9, IBM IDS 9.30.UC2
>
> The only difference in the hardware on these 2 servers is Server A has
> 6 Gigabytes of RAM while Server B has 4. The SSA Disk subsystems are
> identical.
>

They are not identical at all. The informix versions between A and B are
different. I have certainly seen exactly the problem you are experiencing
simply from changing the engine and nothing else.

I would monitor the processes as they run, chances are that only one or 2
sql statements are impacted. You should be able to spot them easily enough
using onstat -g ses <sid> or onstat -g sql <sid>.

Once you have identified the problem queries I would run the statements with
'set explain on' to see the query plans. The differences are likely to be
obvious. I would then use optimizer directives to force B to execute the
query in the same manner as A. If that works set about running statistics on
B to see if you can improve things [remember to take out the directives]. I
always recommend starting with low on its own [remember to drop
distributions] and work upwards from there.

Mark

sending to informix-list

[Tom Lowrie]

[email]tlowrie@munis.com[/email] (Tom Lowrie) wrote in message news:<e91c92f1.0310020736.6b8acebe@posting.google. com>...

> Greetings,
>
> The problem: I run an identical program on Server A and Server B. On
> Server A the program runs in 12 seconds. On Server B it takes 1.5
> hours.
>
> Server A: IBM RS/6000 H80, AIX 4.3.3.0.11, IBM IDS 7.31.UC7
>
> Server B: IBM RS/6000 S80, AIX 4.3.3.0.9, IBM IDS 9.30.UC2
>
> The only difference in the hardware on these 2 servers is Server A has
> 6 Gigabytes of RAM while Server B has 4. The SSA Disk subsystems are
> identical.
>
> Both servers use the 4J's "front end", installed and compiled on each
> server in accordance with the 4J's installation guidelines.
>
> Both servers are dedicated to running the IDS and the Application
> software only. The application software is identical on both servers.
> The application software resides on the same server as the IDS. Both
> servers are pretty much running at the same load level ? not stressed
> at all. Both appear to have plenty of available memory and plenty of
> available CPU time. There is no I/O issue on either server. In short,
> plenty of hardware for the task at hand.
>
> The data in the DB is identical on both servers. The program that
> generates the update statistics SQL is identical on both servers. The
> indexes are identical on both servers. The total size of the database
> after import is less than 2.5 Gig. The tables being accessed for this
> report are all under 120 Meg.
>
> The onconfig files are identical on both servers except for:
>
> Variable: Server A: Sever B:
> SHMVIRTSIZE 786432 524288
> SHMADD 262144 393216
> STACKSIZE 32 64
>
> The program is a YTD Budget Report that takes a few parameters then
> goes out to the database to collect the data and generate the report.
> Nothing fancy. Nothing spectacular. Just your normal, everyday
> accounting type report.
>
> The speed on system B in all other areas is excellent. Normal "Finds"
> against the largest tables, moving between screens,
> adding/deleting/changing field data, etc all runs fine.
>
> Given this info, where do I look for the problem?

Thanks to all of you who have extended a response. The general
consensus seems to be "update statistics". Here is some
additional, more specific information on my problem:

The entire database was dbexported, dropped, dbimported last
Monday night. This should take care of any "drop distribution"
issues. We have an update statistics program that we use to
generate an update statistics sql. It was used and the resulting
update statistics sql was run against the newly imported database.
Here are the update statistic commands used on the tables
accessed by the glytdbud program:

update statistics medium for table glmaster distributions only;
update statistics high for table glmaster(glma_accttp);
update statistics high for table glmaster(glma_seg1);
update statistics high for table glmaster(glma_obj);
update statistics high for table glmaster(glma_project);
update statistics high for table glmaster(glma_org);
update statistics high for table glmaster(glma_entity);
update statistics low for table glmaster(glma_segx);

update statistics medium for table glfundat distributions only;

update statistics medium for table glmstbal distributions only;
update statistics high for table glmstbal(glmb_org);
update statistics low for table glmstbal(glmb_obj);
update statistics low for table glmstbal(glmb_project);
update statistics low for table glmstbal(glmb_period);
update statistics medium for table glmstbal distributions only;
update statistics high for table glmstbal(glmb_org);
update statistics low for table glmstbal(glmb_obj);
update statistics low for table glmstbal(glmb_project);
update statistics low for table glmstbal(glmb_period);

update statistics medium for table gljehold distributions only;
update statistics high for table gljehold(glje_seg1);
update statistics high for table gljehold(glje_project);
update statistics high for table gljehold(glje_yrpr);
update statistics high for table gljehold(glje_org);
update statistics high for table gljehold(glje_yr);
update statistics low for table gljehold(glje_segrem);
update statistics low for table gljehold(glje_obj);
update statistics low for table gljehold(glje_jnl);
update statistics low for table gljehold(glje_seq);
update statistics low for table gljehold(glje_per);


And now for the dbschemas for the tables listed above:

DBSCHEMA Schema Utility INFORMIX-SQL Version 7.31.UC7
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number AAC#J961997
{ TABLE "munis".glmaster row size = 758 number of columns = 99 index
size = 246 }
create table "munis".glmaster
(
glma_accttp char(1),
glma_seg1 char(4),
glma_segx char(16),
glma_obj char(6),
glma_project char(5),
glma_org char(8),
glma_entity char(1),
glma_desc char(30),
glma_short_desc char(10),
glma_baltp char(1),
glma_stat char(1),
glma_encumb_bud char(1),
glma_budgetary char(1),
glma_ref_org char(8),
glma_ref_obj char(6),
glma_ref_proj char(5),
glma_last_up_date date,
glma_b_sign char(1),
glma_seg2 char(6),
glma_seg3 char(6),
glma_seg4 char(6),
glma_seg5 char(6),
glma_seg6 char(6),
glma_seg7 char(6),
glma_seg8 char(6),
glma_bud_yr smallint,
glma_incep_to_soy decimal(13,2),
glma_bud_ny5 decimal(13,2),
glma_stat_ny5 decimal(7,2),
glma_bud_ny4 decimal(13,2),
glma_stat_ny4 decimal(7,2),
glma_bud_ny3 decimal(13,2),
glma_stat_ny3 decimal(7,2),
glma_bud_ny2 decimal(13,2),
glma_stat_ny2 decimal(7,2),
glma_bud_req1_ny1 decimal(13,2),
glma_bud_req2_ny1 decimal(13,2),
glma_bud_req3_ny1 decimal(13,2),
glma_bud_req4_ny1 decimal(13,2),
glma_bud_req5_ny1 decimal(13,2),
glma_stat1_ny1 decimal(7,2),
glma_stat2_ny1 decimal(7,2),
glma_stat3_ny1 decimal(7,2),
glma_stat4_ny1 decimal(7,2),
glma_stat5_ny1 decimal(7,2),
glma_bud_req1_cy decimal(13,2),
glma_bud_req2_cy decimal(13,2),
glma_bud_req3_cy decimal(13,2),
glma_bud_req4_cy decimal(13,2),
glma_bud_req5_cy decimal(13,2),
glma_orig_bud_cy decimal(13,2),
glma_xfr_bud_incy decimal(13,2),
glma_xfr_bud_outcy decimal(13,2),
glma_cfwd_bud_cy decimal(13,2),
glma_cfwd_bud_xfcy decimal(13,2),
glma_rev_bud_cy decimal(13,2),
glma_memo_bal_cy decimal(13,2),
glma_actual_cy decimal(13,2),
glma_actl_cy_frmly decimal(13,2),
glma_estimate_cy decimal(13,2),
glma_stat_cy decimal(7,2),
glma_encumb_cy decimal(13,2),
glma_encm_cy_frmly decimal(13,2),
glma_req_cy decimal(13,2),
glma_orig_bud_ly1 decimal(13,2),
glma_xf_bud_inly1 decimal(13,2),
glma_xf_bud_outly1 decimal(13,2),
glma_cfwd_bud_ly1 decimal(13,2),
glma_rev_bud_ly1 decimal(13,2),
glma_memo_bal_ly1 decimal(13,2),
glma_actual_ly1 decimal(13,2),
glma_close_ly1 decimal(13,2),
glma_stat_ly1 decimal(7,2),
glma_encumb_ly1 decimal(13,2),
glma_orig_bud_ly2 decimal(13,2),
glma_rev_bud_ly2 decimal(13,2),
glma_actual_ly2 decimal(13,2),
glma_stat_ly2 decimal(7,2),
glma_orig_bud_ly3 decimal(13,2),
glma_rev_bud_ly3 decimal(13,2),
glma_actual_ly3 decimal(13,2),
glma_stat_ly3 decimal(7,2),
glma_actual_ly4 decimal(13,2),
glma_actual_ly5 decimal(13,2),
glma_actual_ly6 decimal(13,2),
glma_actual_ly7 decimal(13,2),
glma_actual_ly8 decimal(13,2),
glma_actual_ly9 decimal(13,2),
glma_actual_ly10 decimal(13,2),
glma_incp_orig_bud decimal(13,2),
glma_incp_rev_bud decimal(13,2),
glma_memo_bal_ny decimal(13,2),
glma_encumb_ny decimal(13,2),
glma_req_ny decimal(13,2),
glma_rev_bud_ny decimal(13,2),
glma_desc_ny char(30),
glma_bud_fctr decimal(5,2),
glma_cfwd_unusd_bd decimal(13,2),
glma_filler char(50),
unique (glma_accttp,glma_seg1,glma_segx,glma_obj,glma_pro ject)
constraint "munis".glmaster1,
unique (glma_seg1,glma_segx,glma_obj,glma_project) constraint
"munis".glmaster2,
unique (glma_org,glma_obj,glma_project) constraint
"munis".glmaster3,
unique (glma_obj,glma_org,glma_project) constraint
"munis".glmaster4,
unique (glma_project,glma_org,glma_obj) constraint
"munis".glmaster5,
unique (glma_entity,glma_org,glma_obj,glma_project) constraint
"munis".glmaster6
);
revoke all on "munis".glmaster from "public";


DBSCHEMA Schema Utility INFORMIX-SQL Version 7.31.UC7
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number AAC#J961997
{ TABLE "munis".glfundat row size = 196 number of columns = 36 index
size = 12 }
create table "munis".glfundat
(
glfu_fund char(4) not null ,
glfu_multyr char(1),
glfu_fundtp char(1),
glfu_std_fy char(1),
glfu_bud_method char(1),
glfu_equate_per1 smallint,
glfu_revolve_fund char(1),
glfu_paul2 char(1),
glfu_fundbal_org char(8),
glfu_fundbal_obj char(6),
glfu_revenue_org char(8),
glfu_revenue_obj char(6),
glfu_expense_org char(8),
glfu_expense_obj char(6),
glfu_encumb_org char(8),
glfu_encumb_obj char(6),
glfu_resencumb_org char(8),
glfu_resencumb_obj char(6),
glfu_pryrenc_org char(8),
glfu_pryrenc_obj char(6),
glfu_approp_org char(8),
glfu_approp_obj char(6),
glfu_estrev_org char(8),
glfu_estrev_obj char(6),
glfu_apprfbal_org char(8),
glfu_apprfbal_obj char(6),
glfu_ap_cash_org char(8),
glfu_ap_cash_obj char(6),
glfu_ap_apay_org char(8),
glfu_ap_apay_obj char(6),
glfu_ap_warr_org char(8),
glfu_ap_warr_obj char(6),
glfu_ap_pcash_org char(8),
glfu_ap_pcash_obj char(6),
glfu_avail_bud char(1),
glfu_bud_cfwd_cd char(1),
unique (glfu_fund) constraint "munis".glfundat1
);
revoke all on "munis".glfundat from "public";


DBSCHEMA Schema Utility INFORMIX-SQL Version 7.31.UC7
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number AAC#J961997
{ TABLE "munis".glmstbal row size = 183 number of columns = 25 index
size = 37 }
create table "munis".glmstbal
(
glmb_org char(8),
glmb_obj char(6),
glmb_project char(5),
glmb_period smallint,
glmb_bud_pct decimal(6,3),
glmb_bud_pct_ny decimal(6,3),
glmb_bud_ny decimal(13,2),
glmb_bud_cy decimal(13,2),
glmb_btfr_cy decimal(13,2),
glmb_encumb_cy decimal(13,2),
glmb_actual_cy decimal(13,2),
glmb_bud_ly decimal(13,2),
glmb_encumb_ly decimal(13,2),
glmb_actual_ly decimal(13,2),
glmb_expend_cypy decimal(13,2),
glmb_encumb_cypy decimal(13,2),
glmb_btfr_ly decimal(13,2),
glmb_bud_ly2 decimal(13,2),
glmb_btfr_ly2 decimal(13,2),
glmb_encumb_ly2 decimal(13,2),
glmb_actual_ly2 decimal(13,2),
glmb_bud_ly3 decimal(13,2),
glmb_btfr_ly3 decimal(13,2),
glmb_encumb_ly3 decimal(13,2),
glmb_actual_ly3 decimal(13,2),
unique (glmb_org,glmb_obj,glmb_project,glmb_period) constraint
"munis".glmstbal1
);
revoke all on "munis".glmstbal from "public";


DBSCHEMA Schema Utility INFORMIX-SQL Version 7.31.UC7
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number AAC#J961997
{ TABLE "munis".gljehold row size = 156 number of columns = 28 index
size = 208 }
create table "munis".gljehold
(
glje_seg1 char(4),
glje_segrem char(16),
glje_obj char(6),
glje_project char(5),
glje_yrpr integer,
glje_jnl integer,
glje_seq smallint,
glje_org char(8),
glje_clerk char(8),
glje_ref1 char(6),
glje_ref3 char(12),
glje_ref2 char(10),
glje_ref4 char(10),
glje_source char(3),
glje_tran_type char(1),
glje_bud_type char(1),
glje_comm char(30),
glje_over_bud char(1),
glje_auto_man char(1),
glje_dr_cr char(1),
glje_yr smallint,
glje_per smallint,
glje_eff_date date,
glje_ent_date date,
glje_gross decimal(13,2),
glje_entity char(1),
glje_auto_rev char(1),
glje_jnl_type char(1),
unique (glje_seg1,glje_segrem,glje_obj,glje_project,glje_ yrpr,glje_jnl,glje_seq)
constraint "munis".gljehold1,
unique (glje_org,glje_obj,glje_project,glje_yrpr,glje_jnl ,glje_seq)
constraint
"munis".gljehold2,
unique (glje_yrpr,glje_jnl,glje_seq) constraint
"munis".gljehold3,
unique (glje_project,glje_org,glje_obj,glje_yrpr,glje_jnl ,glje_seq)
constraint
"munis".gljehold4
);
revoke all on "munis".gljehold from "public";

create unique index "munis".gljehold5 on "munis".gljehold (glje_yr,
glje_per,glje_jnl,glje_seq);


#################### END OF DBSCHEMA OUTPUT


This is getting a bit long, but, for those who were looking for
some "set explain on" and "onstat -g ses <sid>" output, here it is:

1. The first file is the complete sqexplain.out file from the 9.x IDS.

2. The second file is the complete sqexplain.out file from the 7.x IDS

3. The third file is the output written to the sqexplain.out file at
the point where the program seems to just stop. This is what the
sqexplain.out file looks like for about 90% of the 1 hour it took
to run this on the 9.x engine.

4. The output from onstat -g ses <sid> while in that 90% period
described above.


File name: sqexplain-b7x.txt - glytdbud using "set explain on".
Taken from 7.x - this runs to completion in about 12 seconds. At
this point, the user can select the type of output desired such
as to a printer, a spool file, the screen or exit. This file should
be extremely similar to sqexplain-b9x.txt, shown next.


QUERY:
------
DELETE FROM wktbl

Estimated Cost: 2
Estimated # of Rows Returned: 10

1) root.wktbl: SEQUENTIAL SCAN


QUERY:
------
DELETE FROM tmpje

Estimated Cost: 2
Estimated # of Rows Returned: 10

1) root.tmpje: SEQUENTIAL SCAN


QUERY:
------
select glmaster.glma_seg1, glmaster.glma_segx, glmaster.glma_accttp,
glmaster.glma_desc, glmaster.glma_short_desc,
glmaster.glma_orig_bud_cy, glmaster.glma_xfr_bud_incy,
glmaster.glma_xfr_bud_outcy, glmaster.glma_cfwd_bud_cy,
glmaster.glma_cfwd_bud_xfcy, glmaster.glma_rev_bud_cy,
glmaster.glma_memo_bal_cy, glmaster.glma_encumb_cy,
glmaster.glma_orig_bud_ly1, glmaster.glma_rev_bud_ly1,
glmaster.glma_memo_bal_ly1, glmaster.glma_encumb_ly1,
glmaster.glma_orig_bud_ly2, glmaster.glma_rev_bud_ly2,
glmaster.glma_orig_bud_ly3, glmaster.glma_rev_bud_ly3,
glmaster.glma_rev_bud_ny, glmaster.glma_memo_bal_ny,
glmaster.glma_encumb_ny, glmaster.glma_incep_to_soy,
glmaster.glma_incp_rev_bud, glmaster.glma_actl_cy_frmly,
glmaster.glma_encm_cy_frmly, glmaster.glma_req_cy,
glmaster.glma_req_ny from glmaster where glmaster.glma_org = ? and
glmaster.glma_obj = ? and glmaster.glma_project = ?

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) munis.glmaster: INDEX PATH

(1) Index Keys: glma_org glma_obj glma_project
Lower Index Filter: (munis.glmaster.glma_org = '2001068 ' AND
(munis.glmaster.glma_obj = '0221 ' AND munis.glmaster.glma_project =
' ' ) )


QUERY:
------
select glfu_multyr, glfu_bud_method, glfu_avail_bud from glfundat
where glfu_fund = ?

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) munis.glfundat: INDEX PATH

(1) Index Keys: glfu_fund
Lower Index Filter: munis.glfundat.glfu_fund = '1 '


QUERY:
------
select glmstbal.glmb_actual_cy, glmstbal.glmb_encumb_cy,
glmstbal.glmb_bud_cy, glmstbal.glmb_btfr_cy, glmstbal.glmb_actual_ly,
glmstbal.glmb_encumb_ly, glmstbal.glmb_bud_ly, glmstbal.glmb_period
from glmstbal where glmstbal.glmb_org = ? and glmstbal.glmb_obj = ?
and glmstbal.glmb_project = ? and glmstbal.glmb_period <= ?

Estimated Cost: 3
Estimated # of Rows Returned: 1

1) munis.glmstbal: INDEX PATH

(1) Index Keys: glmb_org glmb_obj glmb_project glmb_period
Lower Index Filter: (munis.glmstbal.glmb_org = '2001068 ' AND
(munis.glmstbal.glmb_obj = '0221 ' AND munis.glmstbal.glmb_project =
' ' ) )
Upper Index Filter: munis.glmstbal.glmb_period <= 3


QUERY:
------
select sum(glmstbal.glmb_btfr_cy) from glmstbal where
glmstbal.glmb_org = ? and glmstbal.glmb_obj = ? and
glmstbal.glmb_project = ? and glmstbal.glmb_period > ?

Estimated Cost: 4
Estimated # of Rows Returned: 1

1) munis.glmstbal: INDEX PATH

(1) Index Keys: glmb_org glmb_obj glmb_project glmb_period
Lower Index Filter: (munis.glmstbal.glmb_org = '2001068 ' AND
(munis.glmstbal.glmb_obj = '0221 ' AND (munis.glmstbal.glmb_project =
' ' AND munis.glmstbal.glmb_period > 3 ) ) )


QUERY:
------
select glje_yr, glje_per, gljehold.glje_gross, gljehold.glje_dr_cr,
gljehold.glje_bud_type from gljehold where glje_org = ? and glje_obj =
? and glje_project = ? and glje_yr = ? and glje_per > ? and
glje_tran_type = ? and glje_gross IS NOT NULL

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.gljehold: INDEX PATH

Filters: (munis.gljehold.glje_yr = 2004 AND
(munis.gljehold.glje_per > 3 AND (munis.gljehold.glje_tran_type = '5'
AND munis.gljehold.glje_gross IS NOT NULL ) ) )

(1) Index Keys: glje_org glje_obj glje_project glje_yrpr glje_jnl
glje_seq
Lower Index Filter: (munis.gljehold.glje_org = '2001068 ' AND
(munis.gljehold.glje_obj = '0221 ' AND munis.gljehold.glje_project =
' ' ) )


QUERY:
------
select glje_yr, glje_per, gljehold.glje_gross, gljehold.glje_dr_cr,
gljehold.glje_bud_type from gljehold where glje_org = ? and glje_obj =
? and glje_project = ? and glje_yr = ? and glje_per <= ? and
glje_tran_type = ? and glje_gross IS NOT NULL

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.gljehold: INDEX PATH

Filters: (munis.gljehold.glje_yr = 2004 AND
(munis.gljehold.glje_per <= 3 AND (munis.gljehold.glje_tran_type = '4'
AND munis.gljehold.glje_gross IS NOT NULL ) ) )

(1) Index Keys: glje_org glje_obj glje_project glje_yrpr glje_jnl
glje_seq
Lower Index Filter: (munis.gljehold.glje_org = '2001068 ' AND
(munis.gljehold.glje_obj = '0221 ' AND munis.gljehold.glje_project =
' ' ) )


QUERY:
------
select glmaster.glma_seg2, glmaster.glma_org from glmaster where
glmaster.glma_org = ? and glmaster.glma_obj = ? and
glmaster.glma_project = ?

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) munis.glmaster: INDEX PATH

(1) Index Keys: glma_org glma_obj glma_project
Lower Index Filter: (munis.glmaster.glma_org = '2001068 ' AND
(munis.glmaster.glma_obj = '0221 ' AND munis.glmaster.glma_project =
' ' ) )


QUERY:
------
insert into wktbl values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Estimated Cost: 2
Estimated # of Rows Returned: 10


QUERY:
------
SELECT COUNT (*) FROM wktbl

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) root.wktbl: INDEX PATH

(1) Index Keys: (count)


################### END OF FILE 1 OUTPUT


File name: sqexplain-b9x.txt - glytdbud using "set explain on".
Taken from 9.x - this runs to completion in about 1 hour. At
this point, the user can select the type of output desired such
as to a printer, a spool file, the screen or exit. This file should
be extremely similar to sqexplain-b7x.txt, shown above.


QUERY:
------
DELETE FROM wktbl

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) ccssupp.wktbl: SEQUENTIAL SCAN


QUERY:
------
DELETE FROM tmpje

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) ccssupp.tmpje: SEQUENTIAL SCAN


QUERY:
------
select glmaster.glma_seg1, glmaster.glma_segx, glmaster.glma_accttp,
glmaster.glma_desc, glmaster.glma_short_desc,
glmaster.glma_orig_bud_cy, glmaster.glma_xfr_bud_incy,
glmaster.glma_xfr_bud_outcy, glmaster.glma_cfwd_bud_cy,
glmaster.glma_cfwd_bud_xfcy, glmaster.glma_rev_bud_cy,
glmaster.glma_memo_bal_cy, glmaster.glma_encumb_cy,
glmaster.glma_orig_bud_ly1, glmaster.glma_rev_bud_ly1,
glmaster.glma_memo_bal_ly1, glmaster.glma_encumb_ly1,
glmaster.glma_orig_bud_ly2, glmaster.glma_rev_bud_ly2,
glmaster.glma_orig_bud_ly3, glmaster.glma_rev_bud_ly3,
glmaster.glma_rev_bud_ny, glmaster.glma_memo_bal_ny,
glmaster.glma_encumb_ny, glmaster.glma_incep_to_soy,
glmaster.glma_incp_rev_bud, glmaster.glma_actl_cy_frmly,
glmaster.glma_encm_cy_frmly, glmaster.glma_req_cy,
glmaster.glma_req_ny from glmaster where glmaster.glma_org = ? and
glmaster.glma_obj = ? and glmaster.glma_project = ?

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.glmaster: INDEX PATH

(1) Index Keys: glma_org glma_obj glma_project (Serial,
fragments: ALL)
Lower Index Filter: ((munis.glmaster.glma_org = '2001068 ' AND
munis.glmaster.glma_obj = '0130 ' ) AND munis.glmaster.glma_project =
' ' )


QUERY:
------
select glfu_multyr, glfu_bud_method, glfu_avail_bud from glfundat
where glfu_fund = ?

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.glfundat: INDEX PATH

(1) Index Keys: glfu_fund (Serial, fragments: ALL)
Lower Index Filter: munis.glfundat.glfu_fund = '1 '


QUERY:
------
select glmstbal.glmb_actual_cy, glmstbal.glmb_encumb_cy,
glmstbal.glmb_bud_cy, glmstbal.glmb_btfr_cy, glmstbal.glmb_actual_ly,
glmstbal.glmb_encumb_ly, glmstbal.glmb_bud_ly, glmstbal.glmb_period
from glmstbal where glmstbal.glmb_org = ? and glmstbal.glmb_obj = ?
and glmstbal.glmb_project = ? and glmstbal.glmb_period <= ?

Estimated Cost: 4
Estimated # of Rows Returned: 1

1) munis.glmstbal: INDEX PATH

(1) Index Keys: glmb_org glmb_obj glmb_project glmb_period
(Serial, fragments: ALL)
Lower Index Filter: ((munis.glmstbal.glmb_org = '2001068 ' AND
munis.glmstbal.glmb_obj = '0130 ' ) AND munis.glmstbal.glmb_project =
' ' )
Upper Index Filter: munis.glmstbal.glmb_period <= 3


QUERY:
------
select sum(glmstbal.glmb_btfr_cy) from glmstbal where
glmstbal.glmb_org = ? and glmstbal.glmb_obj = ? and
glmstbal.glmb_project = ? and glmstbal.glmb_period > ?

Estimated Cost: 4
Estimated # of Rows Returned: 1

1) munis.glmstbal: INDEX PATH

(1) Index Keys: glmb_org glmb_obj glmb_project glmb_period
(Serial, fragments: ALL)
Lower Index Filter: (((munis.glmstbal.glmb_org = '2001068 '
AND munis.glmstbal.glmb_obj = '0130 ' ) AND
munis.glmstbal.glmb_project = ' ' ) AND munis.glmstbal.glmb_period

> 3 )

QUERY:
------
select glje_yr, glje_per, gljehold.glje_gross, gljehold.glje_dr_cr,
gljehold.glje_bud_type from gljehold where glje_org = ? and glje_obj =
? and glje_project = ? and glje_yr = ? and glje_per > ? and
glje_tran_type = ? and glje_gross IS NOT NULL

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.gljehold: INDEX PATH

Filters: (((munis.gljehold.glje_tran_type = '5' AND
munis.gljehold.glje_per > 3 ) AND munis.gljehold.glje_yr = 2004 ) AND
munis.gljehold.glje_gross IS NOT NULL )

(1) Index Keys: glje_org glje_obj glje_project glje_yrpr glje_jnl
glje_seq (Serial, fragments: ALL)
Lower Index Filter: ((munis.gljehold.glje_obj = '0130 ' AND
munis.gljehold.glje_org = '2001068 ' ) AND munis.gljehold.glje_project
= ' ' )


QUERY:
------
select glje_yr, glje_per, gljehold.glje_gross, gljehold.glje_dr_cr,
gljehold.glje_bud_type from gljehold where glje_org = ? and glje_obj =
? and glje_project = ? and glje_yr = ? and glje_per <= ? and
glje_tran_type = ? and glje_gross IS NOT NULL

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.gljehold: INDEX PATH

Filters: (((munis.gljehold.glje_tran_type = '4' AND
munis.gljehold.glje_yr = 2004 ) AND munis.gljehold.glje_gross IS NOT
NULL ) AND munis.gljehold.glje_per <= 3 )

(1) Index Keys: glje_org glje_obj glje_project glje_yrpr glje_jnl
glje_seq (Serial, fragments: ALL)
Lower Index Filter: ((munis.gljehold.glje_obj = '0130 ' AND
munis.gljehold.glje_org = '2001068 ' ) AND munis.gljehold.glje_project
= ' ' )


QUERY:
------
select glmaster.glma_seg2, glmaster.glma_org from glmaster where
glmaster.glma_org = ? and glmaster.glma_obj = ? and
glmaster.glma_project = ?

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.glmaster: INDEX PATH

(1) Index Keys: glma_org glma_obj glma_project (Serial,
fragments: ALL)
Lower Index Filter: ((munis.glmaster.glma_org = '2001068 ' AND
munis.glmaster.glma_obj = '0130 ' ) AND munis.glmaster.glma_project =
' ' )


QUERY:
------
insert into wktbl values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Estimated Cost: 1
Estimated # of Rows Returned: 1


QUERY:
------
SELECT COUNT (*) FROM wktbl

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) ccssupp.wktbl: INDEX PATH

(1) Index Keys: (count)


################## END OF FILE 2 OUTPUT


File name: sqexplain-a9x.txt - glytdbud using "set explain on".
Taken from 9.x - this is where the output stays for 90% of the
run time. The file onstat-g-ses.explain-a.txt is the output from
onstat -g ses <sid> executed while the sqexplain.out file looked
like this.

QUERY:
------
DELETE FROM wktbl

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) ccssupp.wktbl: SEQUENTIAL SCAN


QUERY:
------
DELETE FROM tmpje

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) ccssupp.tmpje: SEQUENTIAL SCAN


QUERY:
------
select glmaster.glma_seg1, glmaster.glma_segx, glmaster.glma_accttp,
glmaster.glma_desc, glmaster.glma_short_desc,
glmaster.glma_orig_bud_cy, glmaster.glma_xfr_bud_incy,
glmaster.glma_xfr_bud_outcy, glmaster.glma_cfwd_bud_cy,
glmaster.glma_cfwd_bud_xfcy, glmaster.glma_rev_bud_cy,
glmaster.glma_memo_bal_cy, glmaster.glma_encumb_cy,
glmaster.glma_orig_bud_ly1, glmaster.glma_rev_bud_ly1,
glmaster.glma_memo_bal_ly1, glmaster.glma_encumb_ly1,
glmaster.glma_orig_bud_ly2, glmaster.glma_rev_bud_ly2,
glmaster.glma_orig_bud_ly3, glmaster.glma_rev_bud_ly3,
glmaster.glma_rev_bud_ny, glmaster.glma_memo_bal_ny,
glmaster.glma_encumb_ny, glmaster.glma_incep_to_soy,
glmaster.glma_incp_rev_bud, glmaster.glma_actl_cy_frmly,
glmaster.glma_encm_cy_frmly, glmaster.glma_req_cy,
glmaster.glma_req_ny from glmaster where glmaster.glma_org = ? and
glmaster.glma_obj = ? and glmaster.glma_project = ?

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.glmaster: INDEX PATH

(1) Index Keys: glma_org glma_obj glma_project (Serial,
fragments: ALL)
Lower Index Filter: ((munis.glmaster.glma_org = '2001068 ' AND
munis.glmaster.glma_obj = '0130 ' ) AND munis.glmaster.glma_project =
' ' )


QUERY:
------
select glfu_multyr, glfu_bud_method, glfu_avail_bud from glfundat
where glfu_fund = ?

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.glfundat: INDEX PATH

(1) Index Keys: glfu_fund (Serial, fragments: ALL)
Lower Index Filter: munis.glfundat.glfu_fund = '1 '


QUERY:
------
select glmstbal.glmb_actual_cy, glmstbal.glmb_encumb_cy,
glmstbal.glmb_bud_cy, glmstbal.glmb_btfr_cy, glmstbal.glmb_actual_ly,
glmstbal.glmb_encumb_ly, glmstbal.glmb_bud_ly, glmstbal.glmb_period
from glmstbal where glmstbal.glmb_org = ? and glmstbal.glmb_obj = ?
and glmstbal.glmb_project = ? and glmstbal.glmb_period <= ?

Estimated Cost: 4
Estimated # of Rows Returned: 1

1) munis.glmstbal: INDEX PATH

(1) Index Keys: glmb_org glmb_obj glmb_project glmb_period
(Serial, fragments: ALL)
Lower Index Filter: ((munis.glmstbal.glmb_org = '2001068 ' AND
munis.glmstbal.glmb_obj = '0130 ' ) AND munis.glmstbal.glmb_project =
' ' )
Upper Index Filter: munis.glmstbal.glmb_period <= 3


QUERY:
------
select sum(glmstbal.glmb_btfr_cy) from glmstbal where
glmstbal.glmb_org = ? and glmstbal.glmb_obj = ? and
glmstbal.glmb_project = ? and glmstbal.glmb_period > ?

Estimated Cost: 4
Estimated # of Rows Returned: 1

1) munis.glmstbal: INDEX PATH

(1) Index Keys: glmb_org glmb_obj glmb_project glmb_period
(Serial, fragments: ALL)
Lower Index Filter: (((munis.glmstbal.glmb_org = '2001068 '
AND munis.glmstbal.glmb_obj = '0130 ' ) AND
munis.glmstbal.glmb_project = ' ' ) AND munis.glmstbal.glmb_period

> 3 )

QUERY:
------
select glje_yr, glje_per, gljehold.glje_gross, gljehold.glje_dr_cr,
gljehold.glje_bud_type from gljehold where glje_org = ? and glje_obj =
? and glje_project = ? and glje_yr = ? and glje_per > ? and
glje_tran_type = ? and glje_gross IS NOT NULL

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.gljehold: INDEX PATH

Filters: (((munis.gljehold.glje_tran_type = '5' AND
munis.gljehold.glje_per > 3 ) AND munis.gljehold.glje_yr = 2004 ) AND
munis.gljehold.glje_gross IS NOT NULL )

(1) Index Keys: glje_org glje_obj glje_project glje_yrpr glje_jnl
glje_seq (Serial, fragments: ALL)
Lower Index Filter: ((munis.gljehold.glje_obj = '0130 ' AND
munis.gljehold.glje_org = '2001068 ' ) AND munis.gljehold.glje_project
= ' ' )


QUERY:
------
select glje_yr, glje_per, gljehold.glje_gross, gljehold.glje_dr_cr,
gljehold.glje_bud_type from gljehold where glje_org = ? and glje_obj =
? and glje_project = ? and glje_yr = ? and glje_per <= ? and
glje_tran_type = ? and glje_gross IS NOT NULL

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.gljehold: INDEX PATH

Filters: (((munis.gljehold.glje_tran_type = '4' AND
munis.gljehold.glje_yr = 2004 ) AND munis.gljehold.glje_gross IS NOT
NULL ) AND munis.gljehold.glje_per <= 3 )

(1) Index Keys: glje_org glje_obj glje_project glje_yrpr glje_jnl
glje_seq (Serial, fragments: ALL)
Lower Index Filter: ((munis.gljehold.glje_obj = '0130 ' AND
munis.gljehold.glje_org = '2001068 ' ) AND munis.gljehold.glje_project
= ' ' )


QUERY:
------
select glmaster.glma_seg2, glmaster.glma_org from glmaster where
glmaster.glma_org = ? and glmaster.glma_obj = ? and
glmaster.glma_project = ?

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) munis.glmaster: INDEX PATH

(1) Index Keys: glma_org glma_obj glma_project (Serial,
fragments: ALL)
Lower Index Filter: ((munis.glmaster.glma_org = '2001068 ' AND
munis.glmaster.glma_obj = '0130 ' ) AND munis.glmaster.glma_project =
' ' )


QUERY:
------
insert into wktbl values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Estimated Cost: 1
Estimated # of Rows Returned: 1


################### END OF FILE 3 OUTPUT


File name: onstat-g-ses.explain-a - Onstat -g ses <sid>
Taken from 9.x - this is what shows 90% of the run time. See
the file sqexplain-a9x.txt to see what the sqexplain.out file
looked like at this point in the run.

Informix Dynamic Server Version 9.30.UC2 -- On-Line -- Up 22:07:02
-- 1027008 Kbytes

session #RSAM total used
id user tty pid hostname threads memory
memory
4827 ccssupp 6 187216 munis6k 1 737280
708168

tid name rstcb flags curstk status
4947 sqlexec 52598a18 ---PR-- 3224 running

Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
4827 V 511a2020 712704 23264 944 45
4827_SORT_0 V 53e4f020 24576 5848 7 2

name free used name free
used
overhead 0 3296 scb 0 96
opentable 0 24768 filetable 0
1896
ru 0 224 log 0
4200
temprec 0 2376 keys 0
1280
ralloc 0 596328 gentcb 0
1248
ostcb 0 2728 sort 0
17288
sqscb 0 27440 sql 0 40
srtmembuf 0 72 rdahead 0 160
hashfiletab 0 280 osenv 0
1408
buft_buffer 0 8352 sqtcb 0
5112
fragman 0 9200 shmblklist 0 136
sapi 0 120

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
4827 SELECT munis NL Not Wait 0 0 9.03

Current statement name : cu40

Current SQL statement :
select sum(glmstbal.glmb_btfr_cy) from glmstbal where
glmstbal.glmb_org =
? and glmstbal.glmb_obj = ? and glmstbal.glmb_project = ? and
glmstbal.glmb_period > ?

Last parsed SQL statement :
select count(*) from gljehold where gljehold.glje_org = ? and
gljehold.glje_obj = ? and gljehold.glje_project = ? and
(gljehold.glje_gross != 0 and gljehold.glje_gross IS NOT NULL) and
gljehold.glje_yr = 2004 and gljehold.glje_per <= 3

User-created Temp tables :
partnum tabname rowsize
9000ed tmpje 156
8000d3 wktbl 180
700136 tmpacct 24


#################### END OF FILE 4 OUTPUT


And I think that's about enough for now. Again, thanks to those of
you who have extended a response. I have opened a trouble call with
IBM on this and will post whatever conclusion is reached.

If, while looking over this post, you see something that doesn't
look right, or just something you think could be different, let
me know. I run a 7.x IDS on AIX. My experience with 9.x is limited
to whatever I get by touching the box it came in, which sits upon
the shelf uninstalled, primarily due to reading posts like this
one.

Tom