Hi,

We just loaded Informix 9.40 fc2. All is working well except one report that runs through power builder. It seems the stored procedure syntax is not right as you can see. The user sleeps forever. Below is the user that was running the report session info. Is there something we need to do with the stored procedures? We migrated from 7.30.

Thank you so much in advance
Jeff


IBM Informix Dynamic Server Version 9.40.FC2 -- On-Line -- Up 4 days 09:34:3
4 -- 3563520 Kbytes

session #RSAM total used dyna
mic
id user tty pid hostname threads memory memory expl
ain
97099 sgrace PHILA-3F 408 unknown- 1 229376 217416 off

tid name rstcb flags curstk status
95292 sqlexec 2224bc178 ---PR-- 11288 sleeping(Forever)

Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
97099 V 22422d040 163840 10104 261 5
97099_SORT_ V 2299cb040 65536 1856 339 1

name free used name free used
overhead 0 6496 scb 0 144
opentable 0 7832 filetable 0 1920
ru 0 280 misc 0 144
log 0 2184 temprec 0 16248
keys 0 592 ralloc 0 77200
gentcb 0 1680 ostcb 0 3440
sort 0 60552 sqscb 0 21696
sql 0 72 srtmembuf 0 128
rdahead 0 224 hashfiletab 0 552
osenv 0 632 buft_buffer 0 2160
sqtcb 0 5200 fragman 0 7480
shmblklist 0 416 sapi 0 64

sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind dire
ctives
22aa41050 22723d028 0 0 0 0 1


Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain

97099 SELECT custdb DR Not Wait 0 0 7.20 Off


Current statement name : __12_cursor1
Stored procedure stack :

context proc-counter opcode name
------------------------------------------------------------------
0x00000002264fb050 0x2325bace8+0x0080 CURSOR custdb:ar_accrual_rpt_new
0x00000002264fb050 0x2325bace8+0x0080 CURSOR custdb:ar_accrual_rpt_new

Current SQL statement in procedure custdb:ar_accrual_rpt_new
proc-counter 0x2325bace8 opcode CURSOR

"12$cursor1" is select unique custdef.account, custdef.name1, custdef.name2, custdef.repnum, custdef.canceldate, reps.rep_sales
from arbase, custdef, outer(reps) where (and (and (and (= arbase.account, custdef.account), (= custdef.repnum, r
eps.rep_num)), (<> arbase.amount, 0)), (<> arbase.ttype, 101))

Last parsed SQL statement :
execute procedure ar_accrual_rpt_new ( s_atx = 'Y', s_atx_east = 'Y',
s_midwest = 'Y',d_start = DATETIME (2003-11-01) YEAR TO DAY, d_end =
DATETIME (2003-11-30) YEAR TO DAY);

I experienced a similar situation when migrating from IDS 7.31.UC3 to 9.30.UC6 .... performance was dreadful .... but this may not address the root cause of your problem but it won't hurt to try.

At the suggestion of a knowledgeable friend I did the following in dbaccess to each DB:

update statistics drop distributions;

update statistics medium distributions only;

Outside of dbaccess, run whatever script you would normally run to update statistics on the entire DB.

This restored peformance back to acceptable levels

Good luck, hope this works for you