0
1220
Question Jude Mukkadayil · Jul 14, 2023

ODBC connection timeout while loading from big data tables

#InterSystems IRIS #TrakCare #Data Import and Export #ODBC

Hi,

     I am trying to load all the data tables from one iris server to a client server but some of the tables data failing to load all the time. But I can load around 100 tables successfully  but 8 to 10 tables are failing all the time. I made an IRIS odbc connection using odbc driver to load the data from tables.

Also I can see read server loop error message on the iris server side as the same time the table loading fails.

Please find the screen shot attached which shows the error on client server.

 

Can anyone provide me an advise how to fix the issue.

Thanks

Jude

Product version: IRIS 2020.1
Discussion (15)0
Log in or sign up to continue

Comments

Dmitry Maslennikov · Jul 14, 2023

The error on screen shot saying Access Denied, why do you think it's Timeout?

Check the amount of connection available for you, and how much connection you do

0
Jude Mukkadayil · Jul 14, 2023

Hi, Where we can check the amount of connection ? Is it something like a sql timeout ?

Is there any place to give access ?

Thanks for your help.

0
Jude Mukkadayil  Jul 22, 2023 to Luis Angel Pérez Ramos

Still the issue exist .Its not pool connection issue.

0
Jude Mukkadayil · Jul 22, 2023

Still the issue exist . Can anyone advise how to fix the issue.

0
Luis Angel Pérez Ramos  Jul 22, 2023 to Jude Mukkadayil

Have you tried to check "Disable Query Timeout" option on ODBC configuration?

Sorry, my Windows is configured on spanish.

0
Jude Mukkadayil  Jul 22, 2023 to Luis Angel Pérez Ramos

Hi @Luis Angel Pérez Ramos 
                                             , I tried that option but still It got failed.

Any other advise ?

Thanks

0
Jude Mukkadayil  Jul 22, 2023 to Luis Angel Pérez Ramos

Hi @Luis Angel Pérez Ramos 
                                             , I tried that option but still It got failed.

Any other advise ?

Thanks

0
Luis Angel Pérez Ramos  Jul 22, 2023 to Jude Mukkadayil

Please, check the ODBC log, just to confirm what is the exact error.

0
Jude Mukkadayil  Jul 23, 2023 to Luis Angel Pérez Ramos

Please find the ODBC error below.

2023-07-24 02:05:48 [SQLCODE: <-400>:<Fatal error occurred>] [Error: <<READ>ServerLoop+18^%SYS.DBSRV>] [Location: <ServerLoop>] [Client info: <Username: , Node Name:  IP Address: , Executable Name: , Internal Function: DQ>] [%protocol: <57>] $Id: //adhocs-iris/2020.1.0.215.0/TRAKCARE_001/kernel/common/src/aclass.c#1 $ 24006 0

0
Robert Cemper  Jul 22, 2023 to Jude Mukkadayil

Follow up to @Dmitry Maslennikov:
Error on IRIS side:  ACCESS DENIED  ODBC can't connect ===> timeout
Why  ACCESS DENIED ? The most likely cause

  • you run multiple ODBC connections in parallel
  • you use the same fixed USER for access to IRIS
  • maximum license slots by user is ~25
  • once you pass the limit your 25 slots get converted to 25 single licenses
  • any further connection consumes a full license 
  • until you run out of available licenses ==> ACCESS DENIED
  • you can get out of that trap only if ALL connections of that user are closed 

You can avoid this by

  • limiting your ODBC connections below 24
  • or run your ODBC access in  serial mode
  • or use multiple users for access with limit <24
0
Robert Cemper  Jul 22, 2023 to Robert Cemper

BTW:  check consumption of IRIS Licenses

0
Jude Mukkadayil  Jul 22, 2023 to Robert Cemper

Hi @Robert Cemper ,
They are running the queries in a serial mode , ie one after the other. They are using this to load data into SSIS database. But once they reach some of the tables , it will show this error.  This is running fine for 150 tables but the error is showing only for 10 tables.

0
Luis Angel Pérez Ramos  Jul 24, 2023 to Jude Mukkadayil

Could you try to run the data load just for the 10 problematic tables? Just to be sure that is not a specific problem of those tables.

0