0

We have an AS400 mainframe running our DB2 transactional database. We also have a SQL Server setup that gets loaded nightly with data from the AS400. The SQL Server setup is for reporting.

I can link the two database servers, BUT, there's concern about how big a performance hit DB2 might suffer from queries coming from SQL Server.

Basically, the fear is that if we start hitting DB2 with queries from SQL Server we'll bog down the transactional system and screw up orders and shipping.

Thanks in advance for any knowledge that can be shared.

Paul Sweatte
  • 24,148
  • 7
  • 127
  • 265
sothatswhereitgoes
  • 23
  • 5
  • What is the current IBM i machine configuration? Hardware model number, main memory and disk sizes (number of arms in particular)? How many additional transactions per hour? How wide (ie will you be getting 1000 bytes per row or 100) is each transaction - determines network bandwidth as well as disk loading. As an aside, consider pushing the changes from DB2 to SQL Server rather than trying to pull from SQL Server since that would move only changes to SQL Server. – Buck Calabro Jan 15 '14 at 18:59
  • I don't have any of those details but your questions already seem to reveal there is no pat answer to this question. Basically there's a cultural issue whereby anything new is feared and a manager will say a new idea will kill the performance of the system (for instance, there is not one index on any of our SQL Server tables because "indexes make the database run slow". – sothatswhereitgoes Jan 15 '14 at 19:13
  • 1
    The thing we're trying to do is get a report (exported to Excel) to generate in real-time (or very close to real-time). The report is around 1,000 rows, around 16 fields and drawing from around 5 files. – sothatswhereitgoes Jan 15 '14 at 19:16
  • 1
    Any chance you can just execute the query to get those 1,000 rows of 16 fields from 5 files while the IBM i guys are watching and monitoring? If your query has negligible impact and the i guys can see that, perhaps they'll be more comfortable letting you set up your query to run on demand. – Benny Hill Jan 15 '14 at 21:36

2 Answers2

1

Anyone who has a pat answer for a performance question is wrong :-) The appropriate answer is always 'it depends.' Performance tuning is best done via measure, change one variable, repeat.

DB2 for i shouldn't even notice if someone executes a 1,000 row SELECT statement. Take Benny's suggestion and run one while the IBM i side watch. If they want a hint, use WRKACTJOB and sort on the Int column. That represents the interactive response time. I'd guess that the query will be complete before they have time to notice that it was active.

If that seems unacceptable to the management, then perhaps offer to test it before or after hours, where it can't possibly impact interactive performance.

As an aside, the RPG guys can create Excel spreadsheets on the fly too. Scott Klement published some RPG wrappers over the Java POI/HSSF classes. Also, Giovanni Perrotti at Easy400.net has some examples of providing an Excel spreadsheet from a web page.

Buck Calabro
  • 7,558
  • 22
  • 25
  • Completely agree with you Buck. I'll also mention the [DBG/400](http://www.dbg400.net/foswiki/bin/view/DBG400/IndexPage) software which will quickly and easily create CSV files. Obviously CSV's aren't true Excel spreadsheets but one of my clients has been happy with them for well over a decade :-) – Benny Hill Jan 16 '14 at 01:48
  • Thanks to all for the input -- it is absolutely true the best answer regarding performance is, 'it depends'. Unfortunately there won't be a chance to test anything because the issue has devolved to the level of 'don't do anything outside of our comfort zone.' I could probably hit DB2 like a red headed step child and they wouldn't know , but I have other battles to fight. – sothatswhereitgoes Jan 16 '14 at 14:45
  • Re: Excel files. While a 1000-row query, even from multiple files (tables) should be no sweat for even a modest i server, generating a 1000-row Excel file with 16 columns is very, very slow in comparison. And doing so using repeated Java calls from RPG (which is what Scott's stuff does, and Giovanni's stuff seems to just be wrappers for Scott's wrappers) is *extremely* slow. Scott will tell you this himself. – John Y Jan 16 '14 at 16:43
  • 1
    Re: Re: Excel files. That said, it still may be plenty fast enough. There are also other options for generating Excel files directly on the i. PHP and Python are prime examples, and these are both potentially *much* easier to use than the RPG-to-POI interface, especially in mixed-platform shops like it sounds like the OP has. – John Y Jan 16 '14 at 16:49
0

I'd mostly agree with Buck, a 1000 row result set is no big deal...

Unless of course the system is looking through billions of rows across hundreds of tables to get the 1000 rows you are interested in.

Assuming a useful index exists, 1000 rows shouldn't be a big deal. If you have IBM i Access for Windows installed, there's a component of System i Navigator called "Run SQL Scripts" that includes "Visual Explain" that provides a visual explanation of the query execution plan. View that you can ensure that an index is being used.

On key thing, make sure the work is being done on the i. When using a standard linked table MS SQL Server will attempt to pull back all the rows then do it's own "where".

select * from MYLINK.MYIBMI.MYLIB.MYTABE where MYKEYFLD = '00335';

Whereas this format sends the statement to the remote server for processing and just gets back the results:

select * from openquery(MYLINK, 'select * from mylib.mytable where MYKEYFLD = ''00335''');

Alternately, you could ask the i guys to build you a stored procedure that you can call to get back the results you are looking for. Personally, that's my preferred method.

Charles

Charles
  • 21,637
  • 1
  • 20
  • 44