APAR status
Closed as program error.
Error description
A simple select statement that uses concat (||) results in numerous ralloc allocation of size 2104 bytes when the query executes. onstat -g afr <sid> |grep ralloc| egrep 2104 10d250000 2104 ralloc 723 10d251000 2104 ralloc 723 10d252000 2104 ralloc 723 10d253000 2104 ralloc 723 10d254000 2104 ralloc 723 10d255000 2104 ralloc 723 Setting shmtotal to 0, when the query ends, these allocations were returned to session free pool and so remains visible in onstat -g seg in blkfree column. $ onstat -g seg IBM Informix Dynamic Server Version 11.50.FC4W1 -- On-Line -- Up 00:41:41 -- 197632 Kbytes Segment Summary: id key addr size ovhd class blkused blkfree 687866003 52ff4801 10a000000 26214400 740296 R 6149 251 855638164 52ff4802 10b900000 102760448 1205824 V 5855 19233 1593835695 52ff4803 111b00000 10485760 124336 V 31 2529 1811939504 52ff4804 112500000 10485760 124336 V 31 2529 1644167349 52ff4805 112f00000 10485760 124336 V 31 2529 1761607873 52ff4806 113900000 10485760 124336 V 31 2529 1073742019 52ff4807 114300000 10485760 124336 V 31 2529 100663492 52ff4808 114d00000 10485760 124336 V 31 2529 67109062 52ff4809 115700000 10485760 124336 V 31 2529 Total: - - 202375168 - - 12221 37187 (* segment locked in memory) Test query: 1 set PDQPRIORITY 5; 2 set explain on ; 3 select 4 --# INDEX(present present_regtimestamp_idx,present_shopid_idx,present_presentid_id x)} 5 'T14' as tran_id 6 , t.customer_id 7 , '008' as product_code 8 , p.shop_id::lvarchar||'-'||TO_CHAR(p.reg_timestamp,'%Y%m%d') as ref_number 9 --# , p.shop_id::lvarchar as ref_number 10 , '01' as sub_ref_number 11 , extend (p.reg_timestamp,YEAR to DAY ) as hassei_start_date 12 , extend (p.reg_timestamp,YEAR to DAY ) as hassei_end_date 13 , '2009-07-01' as disp_start_date 14 , '2009-07-31' as disp_end_date 15 , ( TODAY - 1 ) as sales_date 16 , ''::int as product_name 17 , ''::int as note 18 , 2 as unit_price 19 , count(*) as quantity 20 , count(*)*2 as total_price 21 , to_char(( current YEAR to MONTH - interval(1) month(2) to month),"%Y%m") as sk_ym 22 , 0 as skms_type 23 from 24 present p 25 , trans_shop t 26 where 27 p.reg_timestamp between '2009-01-01 00:00:00' and '2009-10-31 23:59:59' 28 and p.db_name = "db01" 29 and p.shop_id=t.shop_id 30 and t.customer_id != -1 31 and t.company_id !=22012 32 group by 1,2,3,4,5,6,7,8,9,10,11,12,13,16,17;
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * Users with multi CPU AND PDQ running a query doing a sort * * (i.e group by, order by) AND an expression using system * * cast, cast to a string type or UDT * **************************************************************** * PROBLEM DESCRIPTION: * * A query using concat (||) may consume more memory than * * actually required. * **************************************************************** * RECOMMENDATION: * * Upgrade to IDS 11.50.xC6 and above. * ****************************************************************
Problem conclusion
Problem first fixed in IDS 11.50.xC6.
Temporary fix
Comments
APAR Information
APAR number
IC63445
Reported component name
IBM IDS ENTRP E
Reported component ID
5724L2304
Reported release
B15
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-09-29
Closed date
2009-10-30
Last modified date
2009-10-30
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
IBM IDS ENTRP E
Fixed component ID
5724L2304
Applicable component levels
RB15 PSY
UP
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B15","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
30 October 2009