2522495 - SAP IQ Add dbfile when using filesystem devices could take too long to complete

Sometimes adding a new file to a dbspace could take longer than normal. For e.g in one scenario adding a 4TB file to filesystem device took only 20 secs, whereas adding another file did not complete even after 30mins.

Executing pstacks on the IQ Process will show the following functions:

===== Thread Number  140628592043776  (IQ connID: 0000799996) =====

pc: 0x7fe55d845d42 pcstkwalk(stk_trace*, int, db_log*, hos_fd*)+0x32
pc: 0x7fe55d8463b1 ucstkgentrace(int, int)+0x121
pc: 0x315640f7e0
pc: 0x315640f1a3
pc: 0x7fe55d5be3fd hos_pi_pwrite(hos_fd, void const*, unsigned int, long)+0xd
pc: 0x7fe55d5bf27c hos_pi_alloc_disk(hos_fd, long, int)+0x19c
pc: 0x7fe55d83d178 hos_svbio::PreAllocate(unsigned long long)+0xae
pc: 0x7fe55da5cd1d s_dbmvbio::CreateDiskSegment2(s_dbspace_info const&)+0x18d
pc: 0x7fe55df3a6e6 s_iqstore::CreateDBFile2(s_dbspace_info const&, int)+0x26
pc: 0x7fe55e47e9ad db_CreateDiskSegment::Execute()+0x121d
pc: 0x7fe55e318a62 st_command::DoCmdThroughResourceGate()+0x82
pc: 0x7fe55e4a3c76 db_iqphysddl::CreateDBSpaceCall2(a_statement const*, UIConnection*, UIDBSpace*)+0x26
pc: 0x7fe55d0d6b8d UIQPhysDDL_CreateDBSpaceCall2+0x1d
pc: 0x7fe55e3716c6 st_SAIQInterfaceInfo::callFunction()+0x26
pc: 0x7fe55e3dcfae st_SAIQInterface::RunIQFunc(st_SAIQInterfaceInfo*)+0x1ac
pc: 0x7fe55e37203a st_SAIQInterface::Execute(int, void*, unsigned int (*)+0x20a
pc: 0x7fe55d86b5eb saint_iqthresholdphysddl::AlterDBSpace(a_statement const*, IConnection*, IDBSpace*, unsigned int*)+0x2cb
pc: 0x7fec4ddf7086 dbi_alter_dbspace(Connection*, a_statement*)+0x1236


.iqmsg for slow execution is as follows:

I. 08/01 20:44:26. 0000799996 AlterDBSpace 16387 dbauser
I. 08/01 20:44:26. 0000799996 AlterDBSpace ADDFILE
I. 08/01 20:44:26. 0000799996 CreateDiskSegment
I. 08/01 20:44:26. 0000799996 Checked out license for 100 IQ_VLDBMGMT 
I. 08/01 20:44:26. 0000799996 [20715]: Warning: SYSAM:IQ Main Store Size is 100% full with 0 bytes of free space left.
I. 08/01 20:44:26. 0000799996 [20715]: Warning: SYSAM:Increasing Main Data Store size by 1 bytes will require additional IQ_VLDBMGMT licenses.
I. 08/01 20:44:26. 0000799996 /IQ_USER/user118
I. 08/01 20:44:26. 0000799996 140628592043776 Info: posix_fallocate fd=148 sample time 0.090000 sec.
I. 08/01 20:44:26. 0000799996 140628592043776 Info: posix_fallocate fd=148 second sample time 0.010000 sec.
I. 08/01 20:44:26. 0000799996 140628592043776 Info: posix_fallocate fd=148 sample time 0.010000 sec. 

.iqmsg for fast execution is as follows:

I. 08/01 22:04:59. 0000000016 AlterDBSpace 16387 dbauser
I. 08/01 22:04:59. 0000000016 AlterDBSpace ADDFILE
I. 08/01 22:04:59. 0000000016 CreateDiskSegment
I. 08/01 22:04:59. 0000000016 Checked out license for 100 IQ_VLDBMGMT
I. 08/01 22:04:59. 0000000016 [20715]: Warning: SYSAM:IQ Main Store Size is 100% full with 0 bytes of free space left.
I. 08/01 22:04:59. 0000000016 [20715]: Warning: SYSAM:Increasing Main Data Store size by 1 bytes will require additional IQ_VLDBMGMT licenses.
I. 08/01 22:04:59. 0000000016 /IQ_USER/user118
I. 08/01 22:04:59. 0000000016 139899963422464 Info: posix_fallocate fd=148 sample time 0.000000 sec.
I. 08/01 22:04:59. 0000000016 139899963422464 Info: posix_fallocate fd=148 second sample time 0.000000 sec.
I. 08/01 22:04:59. 0000000016 139899963422464 Info: posix_fallocate fd=148 sample time 0.000000 sec.
I. 08/01 22:04:59. 0000000016 139899963422464 Info: using posix_fallocate fd=148.  Sampling threshold was 0.010000 sec.
...
I. 08/01 22:05:00. 0000000254 Disconnect:  SA connHandle: 1000000241  SA connID: 6  IQ connID: 0000000254  User: dbo

• SAP IQ 16.1
• SAP IQ 16.0
• Unix using filesystem devices

 Sybase , Hang, CR800571 , 800571, pstack , KBA , BC-SYB-IQ , Sybase IQ , How To