MSSQL linked server starts a lot of SQL servers

rene van de bij

New Member
Hi,

I am new on Progress and have a question about the number of SQL servers used in Progress for a query launched via a MSSQL linked server connection (ODBC).

We are using MS SSRS for reports and I noticed that queries with joins in it are starting a lot of SQL servers (logins) in the Progress database.
For instance one query with five joins in it wil start more than 10 servers (logins).
Of course at one point the broker will reject a query reporting that no more SQL servers are available.

Can anyone explain this behaviour and how to prevent this ?

Thanks in advance

René
 

Stefan

Well-Known Member
1. how is this Progress related? SQL Server Reporting Services is firing queries off to the SQL Server database, SQL Server Linked Server functionality is querying Progress.
2. what versions of everything are you using (SQL Server, Progress OpenEdge)?
3a. what happens when you start with one simple query on one table (my favorite is mcomp)?
3b. what happens with one simple query with an inner join to a second table (my favorite is mcomp to comp)?
3c. what happens with one simple query with an inner join to a second table with an inner join to a third table (my favorite is mcomp to comp to ledger)?
 

rene van de bij

New Member
Hi Stefan:
1. SQL SSRS is firing a query to a server object (Linked Server) within the MS SQL environment, the linked server is passing the query to the Progress database via an ODBC connection (looking via SQL Server Profiler, the query is passed without modification i.e. not in several parts).
2. I'm using MS SQL server 2008 R2, Progress 10.2B, Progress ODBC driver pgoe23.dll version 05.30.0109 (b0103,U0073)
3a a simple query on mcomp will start 2 SQL servers (2 logins)
3b a simple query on mcomp with one outer join on comp wil start 4 SQL servers (4 logins)
3c a simple query on mcomp with one outer join on comp and one on ledger wil start 6 SQL servers (6 logins)

Firing the queries from MS SQL Server Management Studio leads to the same results

For each login / started server the next info is written in the logfile :
P-5668 T-2424 I SQLSRV2 9: (-----) SQL Server 10.2B.02 started, configuration: "exactcs.defaultconfiguration"
P-5668 T-2424 I SQLSRV2 9: (-----) "ODBC" started on IPv4 port 3009 for address 0.0.0.0, pid 5668 (0x00001624).
P-5668 T-2424 I SQLSRV2 9: (-----) Thread stack size: 1024000 (bytes).
P-5668 T-2424 I SQLSRV2 9: (-----) DLC from ENVIRONMENT VARIABLE is: C:\Progress\OpenEdge
P-5668 T-2424 I SQLSRV2 9: (-----) WRKDIR from REGISTRY is: C:\OpenEdge\WRK\
P-5668 T-2424 I SQLSRV2 9: (-----) JDKHOME from REGISTRY is: C:\Progress\OpenEdge\jdk
P-5668 T-2424 I SQLSRV2 9: (-----) JREHOME from REGISTRY is: C:\Progress\OpenEdge\jre
P-5668 T-2424 I SQLSRV2 9: (-----) CLASSPATH from DEFAULT is:
P-5668 T-2424 I SQLSRV2 9: (-----) PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds
P-5668 T-4968 I SRV 9: (8873) Login usernum 25, remote SQL client.
P-5668 T-4968 I SRV 9: (7129) Usr 25 set name to sysprogress.
 

Stefan

Well-Known Member
Hi Stefan:
1. SQL SSRS is firing a query to a server object (Linked Server) within the MS SQL environment, the linked server is passing the query to the Progress database via an ODBC connection (looking via SQL Server Profiler, the query is passed without modification i.e. not in several parts).

Good.

3a a simple query on mcomp will start 2 SQL servers (2 logins)
3b a simple query on mcomp with one outer join on comp wil start 4 SQL servers (4 logins)
3c a simple query on mcomp with one outer join on comp and one on ledger wil start 6 SQL servers (6 logins)

So any query is starting two SQL server processes and any join requires an additional query (which costs two additional SQL server processes).

Does this also occur when firing the linked server directly to SQL server (not that I expect this is normal operating procedure)?

Firing the queries from MS SQL Server Management Studio leads to the same results

For each login / started server the next info is written in the logfile :
P-5668 T-2424 I SQLSRV2 9: (-----) SQL Server 10.2B.02 started, configuration: "exactcs.defaultconfiguration"
P-5668 T-2424 I SQLSRV2 9: (-----) "ODBC" started on IPv4 port 3009 for address 0.0.0.0, pid 5668 (0x00001624).
P-5668 T-2424 I SQLSRV2 9: (-----) Thread stack size: 1024000 (bytes).
P-5668 T-2424 I SQLSRV2 9: (-----) DLC from ENVIRONMENT VARIABLE is: C:\Progress\OpenEdge
P-5668 T-2424 I SQLSRV2 9: (-----) WRKDIR from REGISTRY is: C:\OpenEdge\WRK\
P-5668 T-2424 I SQLSRV2 9: (-----) JDKHOME from REGISTRY is: C:\Progress\OpenEdge\jdk
P-5668 T-2424 I SQLSRV2 9: (-----) JREHOME from REGISTRY is: C:\Progress\OpenEdge\jre
P-5668 T-2424 I SQLSRV2 9: (-----) CLASSPATH from DEFAULT is:
P-5668 T-2424 I SQLSRV2 9: (-----) PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds
P-5668 T-4968 I SRV 9: (8873) Login usernum 25, remote SQL client.
P-5668 T-4968 I SRV 9: (7129) Usr 25 set name to sysprogress.

Can you show the log for ONE query (showing the two logins?).
Can you increase logging on the OpenEdge SQL side to show what login 1 and login 2 are thinking they are doing (ie which queries are they executing?)?
 

Stefan

Well-Known Member
OK, I am now executing a query using linked server syntax (and getting the expected result):


Code:
SELECT "adm-nr", "adm-name" FROM dbexactcs.exactcs.PUB.admdat WHERE "adm-nr" = 621


This results, as Rene pointed out, in two server logins - note also the amount of time taken before these 'users' logout:


Code:
[2012/09/18@22:11:34.764+0200] P-83160      T-72760 I SRV     3: (8873)  Login usernum 79, remote SQL client. 
[2012/09/18@22:11:34.795+0200] P-83160      T-72760 I SRV     3: (7129)  Usr 79 set name to sysprogress. 
[2012/09/18@22:11:36.764+0200] P-49428      T-110992 I SRV     4: (8873)  Login usernum 78, remote SQL client. 
[2012/09/18@22:11:36.779+0200] P-49428      T-110992 I SRV     4: (7129)  Usr 78 set name to sysprogress. 
[2012/09/18@22:12:47.517+0200] P-49428      T-110992 I SRV     4: (453)   Logout by sysprogress on  . 
[2012/09/18@22:12:47.533+0200] P-83160      T-72760 I SRV     3: (453)   Logout by sysprogress on  .


To see what is going on on the OpenEdge side of things I enabled logging:


Code:
SET PRO_SERVER LOG ON;


The log files (in the OpenEdge working directory) are then for user 79 (thread 72760):


Code:
20120918_221134 72760: Setting Cursor for SQL Statement: set transaction isolation level READ UNCOMMITTED
20120918_221134 72760: Executing Direct SQL Statement: set transaction isolation level READ UNCOMMITTED
20120918_221134 72760: Freeing Cursor for SQL Statement: set transaction isolation level READ UNCOMMITTED
20120918_221134 72760: Setting Cursor for SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,'' ''), owner, tbl,    rtrim(decode (tbltype, ''C'', ''VIEW'', ''V'', ''VIEW'', ''S'',    ''SYSTEM TABLE'', ''T'',''TABLE''),'' ''), decode(0,1,'' '')from sysprogress.systables where tbltype in (''D'',''T'',''S'',''V'',''C'') AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,'' ''), sowner, sname,    ''SYNONYM'', decode(0,1,'' '')from sysprogress.syssynonyms where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',439,'exactcs',7)
20120918_221134 72760: Preparing SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,'' ''), owner, tbl,    rtrim(decode (tbltype, ''C'', ''VIEW'', ''V'', ''VIEW'', ''S'',    ''SYSTEM TABLE'', ''T'',''TABLE''),'' ''), decode(0,1,'' '')from sysprogress.systables where tbltype in (''D'',''T'',''S'',''V'',''C'') AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,'' ''), sowner, sname,    ''SYNONYM'', decode(0,1,'' '')from sysprogress.syssynonyms where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',439,'exactcs',7)
20120918_221134 72760: Executing SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,'' ''), owner, tbl,    rtrim(decode (tbltype, ''C'', ''VIEW'', ''V'', ''VIEW'', ''S'',    ''SYSTEM TABLE'', ''T'',''TABLE''),'' ''), decode(0,1,'' '')from sysprogress.systables where tbltype in (''D'',''T'',''S'',''V'',''C'') AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,'' ''), sowner, sname,    ''SYNONYM'', decode(0,1,'' '')from sysprogress.syssynonyms where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',439,'exactcs',7)
20120918_221134 72760: Opening SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,'' ''), owner, tbl,    rtrim(decode (tbltype, ''C'', ''VIEW'', ''V'', ''VIEW'', ''S'',    ''SYSTEM TABLE'', ''T'',''TABLE''),'' ''), decode(0,1,'' '')from sysprogress.systables where tbltype in (''D'',''T'',''S'',''V'',''C'') AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,'' ''), sowner, sname,    ''SYNONYM'', decode(0,1,'' '')from sysprogress.syssynonyms where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',439,'exactcs',7)
20120918_221134 72760: Describing SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,'' ''), owner, tbl,    rtrim(decode (tbltype, ''C'', ''VIEW'', ''V'', ''VIEW'', ''S'',    ''SYSTEM TABLE'', ''T'',''TABLE''),'' ''), decode(0,1,'' '')from sysprogress.systables where tbltype in (''D'',''T'',''S'',''V'',''C'') AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,'' ''), sowner, sname,    ''SYNONYM'', decode(0,1,'' '')from sysprogress.syssynonyms where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',439,'exactcs',7)
20120918_221134 72760: Fetching SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,'' ''), owner, tbl,    rtrim(decode (tbltype, ''C'', ''VIEW'', ''V'', ''VIEW'', ''S'',    ''SYSTEM TABLE'', ''T'',''TABLE''),'' ''), decode(0,1,'' '')from sysprogress.systables where tbltype in (''D'',''T'',''S'',''V'',''C'') AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,'' ''), sowner, sname,    ''SYNONYM'', decode(0,1,'' '')from sysprogress.syssynonyms where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',439,'exactcs',7)
20120918_221134 72760: Fetching SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,'' ''), owner, tbl,    rtrim(decode (tbltype, ''C'', ''VIEW'', ''V'', ''VIEW'', ''S'',    ''SYSTEM TABLE'', ''T'',''TABLE''),'' ''), decode(0,1,'' '')from sysprogress.systables where tbltype in (''D'',''T'',''S'',''V'',''C'') AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,'' ''), sowner, sname,    ''SYNONYM'', decode(0,1,'' '')from sysprogress.syssynonyms where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',439,'exactcs',7)
20120918_221134 72760: Freeing Cursor for SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,'' ''), owner, tbl,    rtrim(decode (tbltype, ''C'', ''VIEW'', ''V'', ''VIEW'', ''S'',    ''SYSTEM TABLE'', ''T'',''TABLE''),'' ''), decode(0,1,'' '')from sysprogress.systables where tbltype in (''D'',''T'',''S'',''V'',''C'') AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,'' ''), sowner, sname,    ''SYNONYM'', decode(0,1,'' '')from sysprogress.syssynonyms where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',439,'exactcs',7)
20120918_221135 72760: Setting Cursor for SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), owner, tbl,col ''COLUMN_NAME'', decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' '') ,  decode(coltype,''time'',12,''timestamp'',23,''timestamp_timezone'',32,''bigint'',19,width), odbcinfo(coltype,width,''length''), decode(coltype,''time'',3,''timestamp'',3,''timestamp_timezone'',0,odbcinfo(coltype,ifnull(scale,0),''scale'')), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,''''), rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from sysprogress.syscolumns where 0=0  AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,''''), sowner, sname,col, decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' ''),  width, odbcinfo(coltype,width,''length''), odbcinfo(coltype,ifnull(scale,0),''scale''), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,'''') , rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from  {oj sysprogress.syssynonyms left outer join sysprogress.syscolumns on tbl = stbl} where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',1870,'exactcs',7)
20120918_221135 72760: Preparing SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), owner, tbl,col ''COLUMN_NAME'', decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' '') ,  decode(coltype,''time'',12,''timestamp'',23,''timestamp_timezone'',32,''bigint'',19,width), odbcinfo(coltype,width,''length''), decode(coltype,''time'',3,''timestamp'',3,''timestamp_timezone'',0,odbcinfo(coltype,ifnull(scale,0),''scale'')), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,''''), rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from sysprogress.syscolumns where 0=0  AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,''''), sowner, sname,col, decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' ''),  width, odbcinfo(coltype,width,''length''), odbcinfo(coltype,ifnull(scale,0),''scale''), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,'''') , rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from  {oj sysprogress.syssynonyms left outer join sysprogress.syscolumns on tbl = stbl} where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',1870,'exactcs',7)
20120918_221135 72760: Executing SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), owner, tbl,col ''COLUMN_NAME'', decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' '') ,  decode(coltype,''time'',12,''timestamp'',23,''timestamp_timezone'',32,''bigint'',19,width), odbcinfo(coltype,width,''length''), decode(coltype,''time'',3,''timestamp'',3,''timestamp_timezone'',0,odbcinfo(coltype,ifnull(scale,0),''scale'')), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,''''), rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from sysprogress.syscolumns where 0=0  AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,''''), sowner, sname,col, decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' ''),  width, odbcinfo(coltype,width,''length''), odbcinfo(coltype,ifnull(scale,0),''scale''), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,'''') , rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from  {oj sysprogress.syssynonyms left outer join sysprogress.syscolumns on tbl = stbl} where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',1870,'exactcs',7)
20120918_221135 72760: Opening SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), owner, tbl,col ''COLUMN_NAME'', decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' '') ,  decode(coltype,''time'',12,''timestamp'',23,''timestamp_timezone'',32,''bigint'',19,width), odbcinfo(coltype,width,''length''), decode(coltype,''time'',3,''timestamp'',3,''timestamp_timezone'',0,odbcinfo(coltype,ifnull(scale,0),''scale'')), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,''''), rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from sysprogress.syscolumns where 0=0  AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,''''), sowner, sname,col, decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' ''),  width, odbcinfo(coltype,width,''length''), odbcinfo(coltype,ifnull(scale,0),''scale''), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,'''') , rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from  {oj sysprogress.syssynonyms left outer join sysprogress.syscolumns on tbl = stbl} where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',1870,'exactcs',7)
20120918_221135 72760: Describing SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), owner, tbl,col ''COLUMN_NAME'', decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' '') ,  decode(coltype,''time'',12,''timestamp'',23,''timestamp_timezone'',32,''bigint'',19,width), odbcinfo(coltype,width,''length''), decode(coltype,''time'',3,''timestamp'',3,''timestamp_timezone'',0,odbcinfo(coltype,ifnull(scale,0),''scale'')), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,''''), rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from sysprogress.syscolumns where 0=0  AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,''''), sowner, sname,col, decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' ''),  width, odbcinfo(coltype,width,''length''), odbcinfo(coltype,ifnull(scale,0),''scale''), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,'''') , rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from  {oj sysprogress.syssynonyms left outer join sysprogress.syscolumns on tbl = stbl} where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',1870,'exactcs',7)
20120918_221135 72760: Fetching SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), owner, tbl,col ''COLUMN_NAME'', decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' '') ,  decode(coltype,''time'',12,''timestamp'',23,''timestamp_timezone'',32,''bigint'',19,width), odbcinfo(coltype,width,''length''), decode(coltype,''time'',3,''timestamp'',3,''timestamp_timezone'',0,odbcinfo(coltype,ifnull(scale,0),''scale'')), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,''''), rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from sysprogress.syscolumns where 0=0  AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,''''), sowner, sname,col, decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' ''),  width, odbcinfo(coltype,width,''length''), odbcinfo(coltype,ifnull(scale,0),''scale''), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,'''') , rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from  {oj sysprogress.syssynonyms left outer join sysprogress.syscolumns on tbl = stbl} where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',1870,'exactcs',7)
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching...
20120918_221135 72760: Fetching SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), owner, tbl,col ''COLUMN_NAME'', decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' '') ,  decode(coltype,''time'',12,''timestamp'',23,''timestamp_timezone'',32,''bigint'',19,width), odbcinfo(coltype,width,''length''), decode(coltype,''time'',3,''timestamp'',3,''timestamp_timezone'',0,odbcinfo(coltype,ifnull(scale,0),''scale'')), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,''''), rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from sysprogress.syscolumns where 0=0  AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,''''), sowner, sname,col, decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' ''),  width, odbcinfo(coltype,width,''length''), odbcinfo(coltype,ifnull(scale,0),''scale''), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,'''') , rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from  {oj sysprogress.syssynonyms left outer join sysprogress.syscolumns on tbl = stbl} where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',1870,'exactcs',7)
20120918_221135 72760: Freeing Cursor for SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), owner, tbl,col ''COLUMN_NAME'', decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' '') ,  decode(coltype,''time'',12,''timestamp'',23,''timestamp_timezone'',32,''bigint'',19,width), odbcinfo(coltype,width,''length''), decode(coltype,''time'',3,''timestamp'',3,''timestamp_timezone'',0,odbcinfo(coltype,ifnull(scale,0),''scale'')), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,''''), rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from sysprogress.syscolumns where 0=0  AND owner = ''PUB'' AND tbl = ''admdat'' UNION select decode(0,1,''''), sowner, sname,col, decode(odbcinfo(coltype,0,''datatype''),9,91,10,92,11,93,odbcinfo(coltype,0,''datatype'')), rtrim(decode(coltype,''double'',''double precision'',coltype),'' ''),  width, odbcinfo(coltype,width,''length''), odbcinfo(coltype,ifnull(scale,0),''scale''), odbcinfo(coltype,0,''radix''), convert(''integer'',decode(nullflag,''Y'',1,''N'',0)), decode(0,1,'''') , rtrim(decode(nvl(dflt_value,''N''),''N'',''NULL'',dflt_value)), decode (odbcinfo (coltype,0,''data_type''),7,9,9,9,10,9,odbcinfo (coltype,0,''data_type'')), decode (odbcinfo (coltype,0,''data_type''),7,1,9,2,10,3,0), convert(''integer'',decode(coltype,''character'',width,''varchar'',width,0)), id-1,  decode(nullflag,''YES'',1,''NO'',0) from  {oj sysprogress.syssynonyms left outer join sysprogress.syscolumns on tbl = stbl} where 0=0  AND sowner = ''PUB'' AND sname = ''admdat''',1870,'exactcs',7)
20120918_221135 72760: Setting Cursor for SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221135 72760: Preparing SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221135 72760: Describing SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221135 72760: Opening SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221135 72760: Fetching SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221135 72760: Fetching SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 72760: Fetching SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 72760: Freeing Cursor for SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 72760: Setting Cursor for SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), sysprogress.systables.owner TABLE_SCHEM,    sysprogress.systables.tbl TABLE_NAME,convert(''smallint'',decode (0, 1, '''')) NON_UNIQUE,    decode (0, 1, sysprogress.systables.owner) INDEX_QUALIFIER,    decode (0, 1, sysprogress.systables.owner) INDEX_NAME, convert(''smallint'',0),    convert(''smallint'',decode (0, 1, '''')) ORDINAL_POSITION,    decode (0, 1, sysprogress.systables.owner) COLUMN_NAME,    decode (0, 1, sysprogress.systables.owner) COLLATION,    decode (0, 1, '''') CARDINALITY, decode (0, 1, '''') PAGES, decode (0, 1, '''')     from sysprogress.systables where sysprogress.systables.tbl = ''admdat'' AND sysprogress.systables.owner = ''PUB'' union all select decode(0,1,'''') TABLE_QUALIFIER,    sysprogress.sysindexes.tblowner TABLE_OWNER, sysprogress.sysindexes.tbl TABLE_NAME,    convert(''smallint'',decode (sysprogress.sysindexes.idxtype, ''U'',0 , ''D'', 1)) NON_UNIQUE,    sysprogress.sysindexes.idxowner INDEX_QUALIFIER, sysprogress.sysindexes.idxname INDEX_NAME,    convert(''smallint'',decode(sysprogress.sysindexes.idxmethod,''H'',2,3 )),    convert(''smallint'',sysprogress.sysindexes.idxseq + 1) SEQ_IN_INDEX,    sysprogress.sysindexes.colname COLUMN_NAME,    cast(sysprogress.sysindexes.idxorder as char(1)) ASC_OR_DESC,    decode(0,1,'''') CARDINALITY, decode(0,1,'''') PAGES,decode (0, 1, '''')     from sysprogress.sysindexes where sysprogress.sysindexes.tbl = ''admdat'' AND sysprogress.sysindexes.idxowner = ''PUB''',1427,'exactcs',7)
20120918_221136 72760: Preparing SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), sysprogress.systables.owner TABLE_SCHEM,    sysprogress.systables.tbl TABLE_NAME,convert(''smallint'',decode (0, 1, '''')) NON_UNIQUE,    decode (0, 1, sysprogress.systables.owner) INDEX_QUALIFIER,    decode (0, 1, sysprogress.systables.owner) INDEX_NAME, convert(''smallint'',0),    convert(''smallint'',decode (0, 1, '''')) ORDINAL_POSITION,    decode (0, 1, sysprogress.systables.owner) COLUMN_NAME,    decode (0, 1, sysprogress.systables.owner) COLLATION,    decode (0, 1, '''') CARDINALITY, decode (0, 1, '''') PAGES, decode (0, 1, '''')     from sysprogress.systables where sysprogress.systables.tbl = ''admdat'' AND sysprogress.systables.owner = ''PUB'' union all select decode(0,1,'''') TABLE_QUALIFIER,    sysprogress.sysindexes.tblowner TABLE_OWNER, sysprogress.sysindexes.tbl TABLE_NAME,    convert(''smallint'',decode (sysprogress.sysindexes.idxtype, ''U'',0 , ''D'', 1)) NON_UNIQUE,    sysprogress.sysindexes.idxowner INDEX_QUALIFIER, sysprogress.sysindexes.idxname INDEX_NAME,    convert(''smallint'',decode(sysprogress.sysindexes.idxmethod,''H'',2,3 )),    convert(''smallint'',sysprogress.sysindexes.idxseq + 1) SEQ_IN_INDEX,    sysprogress.sysindexes.colname COLUMN_NAME,    cast(sysprogress.sysindexes.idxorder as char(1)) ASC_OR_DESC,    decode(0,1,'''') CARDINALITY, decode(0,1,'''') PAGES,decode (0, 1, '''')     from sysprogress.sysindexes where sysprogress.sysindexes.tbl = ''admdat'' AND sysprogress.sysindexes.idxowner = ''PUB''',1427,'exactcs',7)
20120918_221136 72760: Executing SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), sysprogress.systables.owner TABLE_SCHEM,    sysprogress.systables.tbl TABLE_NAME,convert(''smallint'',decode (0, 1, '''')) NON_UNIQUE,    decode (0, 1, sysprogress.systables.owner) INDEX_QUALIFIER,    decode (0, 1, sysprogress.systables.owner) INDEX_NAME, convert(''smallint'',0),    convert(''smallint'',decode (0, 1, '''')) ORDINAL_POSITION,    decode (0, 1, sysprogress.systables.owner) COLUMN_NAME,    decode (0, 1, sysprogress.systables.owner) COLLATION,    decode (0, 1, '''') CARDINALITY, decode (0, 1, '''') PAGES, decode (0, 1, '''')     from sysprogress.systables where sysprogress.systables.tbl = ''admdat'' AND sysprogress.systables.owner = ''PUB'' union all select decode(0,1,'''') TABLE_QUALIFIER,    sysprogress.sysindexes.tblowner TABLE_OWNER, sysprogress.sysindexes.tbl TABLE_NAME,    convert(''smallint'',decode (sysprogress.sysindexes.idxtype, ''U'',0 , ''D'', 1)) NON_UNIQUE,    sysprogress.sysindexes.idxowner INDEX_QUALIFIER, sysprogress.sysindexes.idxname INDEX_NAME,    convert(''smallint'',decode(sysprogress.sysindexes.idxmethod,''H'',2,3 )),    convert(''smallint'',sysprogress.sysindexes.idxseq + 1) SEQ_IN_INDEX,    sysprogress.sysindexes.colname COLUMN_NAME,    cast(sysprogress.sysindexes.idxorder as char(1)) ASC_OR_DESC,    decode(0,1,'''') CARDINALITY, decode(0,1,'''') PAGES,decode (0, 1, '''')     from sysprogress.sysindexes where sysprogress.sysindexes.tbl = ''admdat'' AND sysprogress.sysindexes.idxowner = ''PUB''',1427,'exactcs',7)
20120918_221136 72760: Opening SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), sysprogress.systables.owner TABLE_SCHEM,    sysprogress.systables.tbl TABLE_NAME,convert(''smallint'',decode (0, 1, '''')) NON_UNIQUE,    decode (0, 1, sysprogress.systables.owner) INDEX_QUALIFIER,    decode (0, 1, sysprogress.systables.owner) INDEX_NAME, convert(''smallint'',0),    convert(''smallint'',decode (0, 1, '''')) ORDINAL_POSITION,    decode (0, 1, sysprogress.systables.owner) COLUMN_NAME,    decode (0, 1, sysprogress.systables.owner) COLLATION,    decode (0, 1, '''') CARDINALITY, decode (0, 1, '''') PAGES, decode (0, 1, '''')     from sysprogress.systables where sysprogress.systables.tbl = ''admdat'' AND sysprogress.systables.owner = ''PUB'' union all select decode(0,1,'''') TABLE_QUALIFIER,    sysprogress.sysindexes.tblowner TABLE_OWNER, sysprogress.sysindexes.tbl TABLE_NAME,    convert(''smallint'',decode (sysprogress.sysindexes.idxtype, ''U'',0 , ''D'', 1)) NON_UNIQUE,    sysprogress.sysindexes.idxowner INDEX_QUALIFIER, sysprogress.sysindexes.idxname INDEX_NAME,    convert(''smallint'',decode(sysprogress.sysindexes.idxmethod,''H'',2,3 )),    convert(''smallint'',sysprogress.sysindexes.idxseq + 1) SEQ_IN_INDEX,    sysprogress.sysindexes.colname COLUMN_NAME,    cast(sysprogress.sysindexes.idxorder as char(1)) ASC_OR_DESC,    decode(0,1,'''') CARDINALITY, decode(0,1,'''') PAGES,decode (0, 1, '''')     from sysprogress.sysindexes where sysprogress.sysindexes.tbl = ''admdat'' AND sysprogress.sysindexes.idxowner = ''PUB''',1427,'exactcs',7)
20120918_221136 72760: Describing SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), sysprogress.systables.owner TABLE_SCHEM,    sysprogress.systables.tbl TABLE_NAME,convert(''smallint'',decode (0, 1, '''')) NON_UNIQUE,    decode (0, 1, sysprogress.systables.owner) INDEX_QUALIFIER,    decode (0, 1, sysprogress.systables.owner) INDEX_NAME, convert(''smallint'',0),    convert(''smallint'',decode (0, 1, '''')) ORDINAL_POSITION,    decode (0, 1, sysprogress.systables.owner) COLUMN_NAME,    decode (0, 1, sysprogress.systables.owner) COLLATION,    decode (0, 1, '''') CARDINALITY, decode (0, 1, '''') PAGES, decode (0, 1, '''')     from sysprogress.systables where sysprogress.systables.tbl = ''admdat'' AND sysprogress.systables.owner = ''PUB'' union all select decode(0,1,'''') TABLE_QUALIFIER,    sysprogress.sysindexes.tblowner TABLE_OWNER, sysprogress.sysindexes.tbl TABLE_NAME,    convert(''smallint'',decode (sysprogress.sysindexes.idxtype, ''U'',0 , ''D'', 1)) NON_UNIQUE,    sysprogress.sysindexes.idxowner INDEX_QUALIFIER, sysprogress.sysindexes.idxname INDEX_NAME,    convert(''smallint'',decode(sysprogress.sysindexes.idxmethod,''H'',2,3 )),    convert(''smallint'',sysprogress.sysindexes.idxseq + 1) SEQ_IN_INDEX,    sysprogress.sysindexes.colname COLUMN_NAME,    cast(sysprogress.sysindexes.idxorder as char(1)) ASC_OR_DESC,    decode(0,1,'''') CARDINALITY, decode(0,1,'''') PAGES,decode (0, 1, '''')     from sysprogress.sysindexes where sysprogress.sysindexes.tbl = ''admdat'' AND sysprogress.sysindexes.idxowner = ''PUB''',1427,'exactcs',7)
20120918_221136 72760: Fetching SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), sysprogress.systables.owner TABLE_SCHEM,    sysprogress.systables.tbl TABLE_NAME,convert(''smallint'',decode (0, 1, '''')) NON_UNIQUE,    decode (0, 1, sysprogress.systables.owner) INDEX_QUALIFIER,    decode (0, 1, sysprogress.systables.owner) INDEX_NAME, convert(''smallint'',0),    convert(''smallint'',decode (0, 1, '''')) ORDINAL_POSITION,    decode (0, 1, sysprogress.systables.owner) COLUMN_NAME,    decode (0, 1, sysprogress.systables.owner) COLLATION,    decode (0, 1, '''') CARDINALITY, decode (0, 1, '''') PAGES, decode (0, 1, '''')     from sysprogress.systables where sysprogress.systables.tbl = ''admdat'' AND sysprogress.systables.owner = ''PUB'' union all select decode(0,1,'''') TABLE_QUALIFIER,    sysprogress.sysindexes.tblowner TABLE_OWNER, sysprogress.sysindexes.tbl TABLE_NAME,    convert(''smallint'',decode (sysprogress.sysindexes.idxtype, ''U'',0 , ''D'', 1)) NON_UNIQUE,    sysprogress.sysindexes.idxowner INDEX_QUALIFIER, sysprogress.sysindexes.idxname INDEX_NAME,    convert(''smallint'',decode(sysprogress.sysindexes.idxmethod,''H'',2,3 )),    convert(''smallint'',sysprogress.sysindexes.idxseq + 1) SEQ_IN_INDEX,    sysprogress.sysindexes.colname COLUMN_NAME,    cast(sysprogress.sysindexes.idxorder as char(1)) ASC_OR_DESC,    decode(0,1,'''') CARDINALITY, decode(0,1,'''') PAGES,decode (0, 1, '''')     from sysprogress.sysindexes where sysprogress.sysindexes.tbl = ''admdat'' AND sysprogress.sysindexes.idxowner = ''PUB''',1427,'exactcs',7)
20120918_221136 72760: Fetching...
20120918_221136 72760: Fetching...
20120918_221136 72760: Fetching...
20120918_221136 72760: Fetching SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), sysprogress.systables.owner TABLE_SCHEM,    sysprogress.systables.tbl TABLE_NAME,convert(''smallint'',decode (0, 1, '''')) NON_UNIQUE,    decode (0, 1, sysprogress.systables.owner) INDEX_QUALIFIER,    decode (0, 1, sysprogress.systables.owner) INDEX_NAME, convert(''smallint'',0),    convert(''smallint'',decode (0, 1, '''')) ORDINAL_POSITION,    decode (0, 1, sysprogress.systables.owner) COLUMN_NAME,    decode (0, 1, sysprogress.systables.owner) COLLATION,    decode (0, 1, '''') CARDINALITY, decode (0, 1, '''') PAGES, decode (0, 1, '''')     from sysprogress.systables where sysprogress.systables.tbl = ''admdat'' AND sysprogress.systables.owner = ''PUB'' union all select decode(0,1,'''') TABLE_QUALIFIER,    sysprogress.sysindexes.tblowner TABLE_OWNER, sysprogress.sysindexes.tbl TABLE_NAME,    convert(''smallint'',decode (sysprogress.sysindexes.idxtype, ''U'',0 , ''D'', 1)) NON_UNIQUE,    sysprogress.sysindexes.idxowner INDEX_QUALIFIER, sysprogress.sysindexes.idxname INDEX_NAME,    convert(''smallint'',decode(sysprogress.sysindexes.idxmethod,''H'',2,3 )),    convert(''smallint'',sysprogress.sysindexes.idxseq + 1) SEQ_IN_INDEX,    sysprogress.sysindexes.colname COLUMN_NAME,    cast(sysprogress.sysindexes.idxorder as char(1)) ASC_OR_DESC,    decode(0,1,'''') CARDINALITY, decode(0,1,'''') PAGES,decode (0, 1, '''')     from sysprogress.sysindexes where sysprogress.sysindexes.tbl = ''admdat'' AND sysprogress.sysindexes.idxowner = ''PUB''',1427,'exactcs',7)
20120918_221136 72760: Freeing Cursor for SQL Statement: CALL PRO_CATALOG_SCHEMA('select decode(0,1,''''), sysprogress.systables.owner TABLE_SCHEM,    sysprogress.systables.tbl TABLE_NAME,convert(''smallint'',decode (0, 1, '''')) NON_UNIQUE,    decode (0, 1, sysprogress.systables.owner) INDEX_QUALIFIER,    decode (0, 1, sysprogress.systables.owner) INDEX_NAME, convert(''smallint'',0),    convert(''smallint'',decode (0, 1, '''')) ORDINAL_POSITION,    decode (0, 1, sysprogress.systables.owner) COLUMN_NAME,    decode (0, 1, sysprogress.systables.owner) COLLATION,    decode (0, 1, '''') CARDINALITY, decode (0, 1, '''') PAGES, decode (0, 1, '''')     from sysprogress.systables where sysprogress.systables.tbl = ''admdat'' AND sysprogress.systables.owner = ''PUB'' union all select decode(0,1,'''') TABLE_QUALIFIER,    sysprogress.sysindexes.tblowner TABLE_OWNER, sysprogress.sysindexes.tbl TABLE_NAME,    convert(''smallint'',decode (sysprogress.sysindexes.idxtype, ''U'',0 , ''D'', 1)) NON_UNIQUE,    sysprogress.sysindexes.idxowner INDEX_QUALIFIER, sysprogress.sysindexes.idxname INDEX_NAME,    convert(''smallint'',decode(sysprogress.sysindexes.idxmethod,''H'',2,3 )),    convert(''smallint'',sysprogress.sysindexes.idxseq + 1) SEQ_IN_INDEX,    sysprogress.sysindexes.colname COLUMN_NAME,    cast(sysprogress.sysindexes.idxorder as char(1)) ASC_OR_DESC,    decode(0,1,'''') CARDINALITY, decode(0,1,'''') PAGES,decode (0, 1, '''')     from sysprogress.sysindexes where sysprogress.sysindexes.tbl = ''admdat'' AND sysprogress.sysindexes.idxowner = ''PUB''',1427,'exactcs',7)
20120918_221136 72760: Setting Cursor for SQL Statement: set catalog exactcs
20120918_221136 72760: Executing Direct SQL Statement: set catalog exactcs
20120918_221136 72760: Freeing Cursor for SQL Statement: set catalog exactcs
20120918_221136 72760: Setting Cursor for SQL Statement: set catalog exactcs
20120918_221136 72760: Executing Direct SQL Statement: set catalog exactcs
20120918_221136 72760: Freeing Cursor for SQL Statement: set catalog exactcs
20120918_221136 72760: Setting Cursor for SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 72760: Preparing SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 72760: Describing SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 72760: Opening SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 72760: Freeing Cursor for SQL Statement: select * from "exactcs"."PUB"."admdat"


Which seems to be reading the relevant catalog for the definition of the table.


The second login, user 78 (thread 110992) is doing a lot less, but seems to be performing the actual query.


Code:
20120918_221136 110992: Setting Cursor for SQL Statement: set transaction isolation level READ UNCOMMITTED
20120918_221136 110992: Executing Direct SQL Statement: set transaction isolation level READ UNCOMMITTED
20120918_221136 110992: Freeing Cursor for SQL Statement: set transaction isolation level READ UNCOMMITTED
20120918_221136 110992: Setting Cursor for SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 110992: Preparing SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 110992: Describing SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 110992: Opening SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 110992: Freeing Cursor for SQL Statement: select * from "exactcs"."PUB"."admdat"
20120918_221136 110992: Setting Cursor for SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Preparing SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Describing SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Freeing Cursor for SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Setting Cursor for SQL Statement: SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE
20120918_221136 110992: Preparing SQL Statement: SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE
20120918_221136 110992: Returning from sql_fn() with code: -210056
20120918_221136 110992: Freeing Cursor for SQL Statement: SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE
20120918_221136 110992: Setting Cursor for SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Preparing SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Describing SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Opening SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Fetching SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Closing SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)
20120918_221136 110992: Freeing Cursor for SQL Statement: SELECT "Tbl1002"."adm-nr" "Col1004","Tbl1002"."adm-name" "Col1005" FROM "exactcs"."PUB"."admdat" "Tbl1002" WHERE "Tbl1002"."adm-nr"=(621)

So it would seem that query one is getting schema information and query two is then executing the query. The actual query seems to be fired after the schema information query, so I see no reason why the second query is not being executed by the first user / server.
 

Stefan

Well-Known Member
Note to self: next time enable SQL logging with:

Code:
SET PRO_SERVER LOG ON WITH (statement);

Yes, literally 'with (statement)' with brackets...
 

Stefan

Well-Known Member
And this behavior seems to sort of be 'as expected' - see ProKB, although it does not explain why multiple logins are required.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Thanks, I learned something there about SQL logging. Are the log files in the working directory or in the DB directory? The KB says the latter.
 

Stefan

Well-Known Member
Thanks, I learned something there about SQL logging. Are the log files in the working directory or in the DB directory? The KB says the latter.

You're welcome - I did too :)

The files were dumped to the working directory, the KB is wrong. The manual is right:

Logging files are located in the server’s work directory. The work directory corresponds to the value of the WRKDIR environment variable on UNIX systems and the applicable registry settings in Windows systems.
 

Stefan

Well-Known Member
Just checked with OpenEdge 11.0 (32-bit) ODBC driver from SQL 2008R2 (32-bit) to Win7x64 OE11.0 32-bit database. Double user connections per query part still apply.
 
Top