Issue 121856 - JT400 takes too much time to return SQL results
Summary: JT400 takes too much time to return SQL results
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: 3.4.1
Hardware: PC All
: P3 Major (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2013-03-06 09:58 UTC by Amalia
Modified: 2013-03-06 09:58 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description Amalia 2013-03-06 09:58:38 UTC
Connecting from Windows XP + AOO 3.4.1 to ISeries (OS400) v7r1m0 with jt400 takes at least 1 minute.

Testing with IBM developers I’ve been reported this:

Looking over this trace, the problem is in the application design (Open
Office being the application here).                                    
I have broken down the time using the trace data.                      
The total amount of time is 1 minute, 2 seconds:                       
11:44:10.296212  set attributes                                        
11:45:12.501284  disconnect                                            
00:01:02.205072  Total time                                            
Of that time, the application spends most of its time getting metadata.
The first thing it gets is the table types that db2 supports, that take
 0.3 seconds.                                                          
Then it makes a call to get metadata for just about every table on the 
system.  That takes 33.2 seconds:                                      
CALL SYSIBM.SQLTABLES(?,?,?,?,?)                                       
Parameters:  <NULL>, <NULL>, %, ,                                      
11:44:44.004708  end of data retrieval                                 
11:44:10.759204  beginning of prepare of statement                     
00:00:33.245504  total time                                            
Then it spends 13.6 seconds selecting even more metadata:              
CALL SYSIBM.SQLTABLES(?,?,?,?,?)                                       
Parameters:  <NULL>, <NULL>, %, VIEW,                                  
11:44:57.909348  end of data retrieval                                 
11:44:44.293124  beginning of prepare                                  
00:00:13.616224  total time                                            
Then it spends 0.067 seconds actually running the query that you want i
 to run:                                                               
SELECT * FROM "QIWS"."QCUSTCDT"                                        
11:44:58.050436  end of data retrieval                                 
11:44:57.983076  beginning of prepare                                  
00:00:00.067360  total time                                            
Finally it does another strange thing, it calls the following stored   
procedure 11 times with exactly the same parameter data and all of thos
 took a total of 14.4 seconds.                                         
CALL SYSIBM.SQLSPECIALCOLUMNS(?,?,?,?, ?,?,?)                          
Parameters: 2, 'BENDERTS', '', '', 0, 1,                               
That returns an empty result set each time.                            
So the total breakdown is 0.067 seconds to run the query that you want 
it to run and 61 seconds getting metadata that you don't want or need. 
I have no idea why Open Office gets this data or what you might do to  
make it stop but I can point out that the problem isn't the JDBC driver
or the i.  They returned a lot of data quickly to this application but 
the application doesn't need any of that data in order to run your     
query so it would seem to be a lot of wasted effort.                   
This is something that the customer will need to address with the      
application support if they want this to improve.