load table fails with hos_clientfileio.cxx:208,

[Former Member]

IQ Guru's

user trying to load 10GB file from IQ client toIQ server, it fails in the middle of the load. I have this options set on the DB. Any suggestions please -

set option allow_read_client_file='on' ;

grant READCLIENTFILE to user ;

I. 07/15 07:09:01. 0001270195 PostCmt 0

I. 07/15 07:09:02. 0000000000 Cancellation request received:  SA connHandle: 1862  SA connID: 28  IQ connID: 0001270078  User: xxxx

I. 07/15 07:09:02. 0001270078 Exception Thrown from hos_clientfileio.cxx:208, Err# 9, tid 233 origtid 233

I. 07/15 07:09:02. 0001270078    O/S Err#: 0, ErrID: 522 (hos_ioexception); SQLCode: -1006140, SQLState: 'QBB40', Severity: 14

I. 07/15 07:09:02. 0001270078 [20378]: I/O Error on file X:/temp/LOAD/LOAD_201507xx.csv

-- (hos_clientfileio.cxx 208)

I. 07/15 07:09:02. 0001270078 Exception Thrown from hos_clientfileio.cxx:208, Err# 9, tid 8 origtid 233

I. 07/15 07:09:02. 0001270078    O/S Err#: 0, ErrID: 522 (hos_ioexception); SQLCode: -1006140, SQLState: 'QBB40', Severity: 14

I. 07/15 07:09:02. 0001270078 [20378]: I/O Error on file X:/temp/LOAD/LOAD_201507xx.csv

-- (hos_clientfileio.cxx 208)

[former_member303458]

Hi,

I have the same problem. Did you find a solution to this issue?

Regards,

Alejandro Chinchilla

[c_baker]

How long after your last command does the disconnect happen?

If the client is on Windows, then it is possible that Windows TCP settings are disconnecting the session as it appears to be idle, even though it is still running.

Is your topology the same as above - 2 IQ servers on Linux with a Windows client?

Check your Windows registry for TCP/IP 'idle timeout' settings.  Maybe increase that and your number of 'keep alives' and 'keep alive retries' so that your longest session is covered.

What client are you using? Interactive SQL/dbisql from the IQ network client or the ASE isql (OpenClient)?  What versions of clients and IQ servers are running.

Chris

[Former Member]

This job when ran manually runs as success. Fails as par tof the batch stream. This is the only job that's run, so no contention on the server.

[c_baker]

Out of curiosity, how many raw file bytes does 47,700,00 rows represent?  Is it all your rows?

As this is IQ 15.2, could you be into phase 2 of the load and the client connection is being closed by Windows?

Is the IQ server on Windows or another platform?  What EBF level is your IQ?

Chris

[Former Member]

Chris ! Thank you for the quick responses.

No. of rows are 230 million loaded from csv file.

IQ server - IQ/15.4.0.3019/120816/P/ESD 2/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2012-08-16 10:48:47

Client on - Windows 2008R2 Build 7601 Service Pack 1 , IQ version is IQ 15.2.

yes, Connection terminated abnormally; client socket shut down

[c_baker]

The file name is referenced as a client file?  You are using dbisql?

Chris

[Former Member]

chris

yes using dbisql. its a 10 GB file reading from the local drive  onthe client side.

[markmumy]

Did you set the IQ server side options to allow client read file access?

Mark

[Former Member]

Mark, yes I did as

set option allow_read_client_file='on' ;

grant READCLIENTFILE to user ;

on srvlog - I have errors as -

I. 07/15 07:08:21.      47500000 Rows, 1 Seconds

I. 07/15 07:08:21.      47600000 Rows, 0 Seconds

I. 07/15 07:08:21.      47700000 Rows, 0 Seconds

I. 07/15 07:09:02. Connection terminated abnormally; client socket shut down

I. 07/15 07:09:02. Disconnected TCPIP client's AppInfo: IP=xx.xx.xx.x ;HOST=yy ;OSUSER=user1;OS='Windows 2008R2 Build 7601 Service Pack 1';EXE='C:\Program Files (x86)\SYBASE\Sybase IQ 15_2\IQ-15_2\bin32\dbisql.com';PID=0x1178;THREAD=0x2fe8;VERSION=11.0.1.5604;API=iAnywhereJDBC;TIMEZONEADJUSTMENT=-240

[former_member333181]

Hi,

   The error you get,

I. 07/15 07:09:02. 0000000000 Cancellation request received:  SA connHandle: 1862  SA connID: 28  IQ connID: 0001270078  User: xxxx

  And

I. 07/15 07:08:21.      47700000 Rows, 0 Seconds

I. 07/15 07:09:02. Connection terminated abnormally; client socket shut down

I. 07/15 07:09:02. Disconnected TCPIP client's AppInfo: IP=xx.xx.xx.x ;HOST=yy ;OSUSER=user1;OS='Windows 2008R2 Build 7601 Service Pack 1';EXE='C:\Program Files (x86)\SYBASE\Sybase IQ 15_2\IQ-15_2\bin32\dbisql.com';PID=0x1178;THREAD=0x2fe8;VERSION=11.0.1.5604;API=iAnywhereJDBC;TIMEZONEADJUSTMENT=-240

  Indicates that either your firewall or some network setting terminated the connection. I do not think this is an IQ issue or bug.

Regards

Shashi

[Former Member]

Shashikant

Thank you for the response. Its very strange when this job s run manually it completes successfulyl uploading 230 millionrows. If its a firewall or a newwork setting manual run should also fail.

This job is part of the bacth process and support is running it mnaully on a daily basis, need  a resolution or I need to re-confirm before I state is not a issue on the IQ side.

Appreicate your response again.

[former_member333181]

If you can reproduce the problem, then you can check the file descriptor in /proc/IQ_SERVER_PID/fd directory for this socket connection. Also on Linux you can run "strace -f -r -T -tt -s 128 -o OUTPUT_FILE -p IQ_SERVER_PID" . If this error is happening around same row value, then you can wait until it shows message of loading around 47400000 rows and then run strace command, as otherwise OUTPUT_FILE will be large.  Also make sure you send SAP support complete .iqmsg/srvlog file and this strace output file. I assume you opened an incident with support on this issue.

Regards

Shashi

[Former Member]

yes, casae has been opened with SAP. It runs fine when run manually. as suggestedm, I will review the file descriptors

[former_member333181]

Is this IQ server on Windows machine? If yes then you will not be able to run strace or file descriptors checking as I mentioned. I am sorry about that, and as usual I am not at all fan of Windows OS, (Unfortunately I have to use it to connect to my Unix machines 😞 )

Regards

Shashi

[Former Member]

source is Linux Suse / IQ 12.7 -in NY Target  is RHEL IQ 15.4 IL , target bigger page size so maual extract of iqunload followed by data load. having issues withdta load delimters