Moderator: Thank you for joining us for our Live Expert Q&A session with Kent Milligan. Kent is an iSeries Technology Specialist for PartnerWorld. He will show you how you can get the most out of SQL & DB2 UDB for the iSeries.

mtripp595109: Will there ever be any SQL interface for creating triggers on the iSeries?

Kent_Milligan : Yes, that will be included in the 2001 release for iSeries.

ehurley98085: What about DDS? Seems like there are always rumors going around that it's going away?

Kent_Milligan : DDS will continue to be supported. The fact that IBM continues to say that SQL is the strategic interface for the AS/400 confuses most customers. That statement just means that the SQL enhancements will get the majority of the investment.

sneum963435: We are using OPNQRYF for all our pre-selections in CL programs. Would the use of SQL improve processing time?

Kent_Milligan : Possibly, depends on what you're doing with OPNQRYF. SQL is faster at set operations, so if you're doing joins you might see improvement.

bleis81011: What limitations do we have not being on iSeries? We are on a 720 with V4R4.

Kent_Milligan : None really, just the normal differences between releases of the OS/400. In V4R5, there is more DB2 functionality than in V4R4.

greg725911: Does the SQL query optimizer use join logical files to assist it in building joins requested within an SQL statement, similar to how it uses other logical and physical file keys to select and sequence data?

Kent_Milligan : The optimizer considers join logical files, but it usually limits the optimizers choices. I'd recommend not using them, they don't offer any performance advantages since a join logical file doesn't pre-join any of the data.

w.buetikofer711220 : Which version is the DB2 Database on the AS/400?

Kent_Milligan : There is no master version of DB2 UDB, so the AS/400 DB2 version just uses the iSeries/AS/400 version name. There is a white paper on the DB2 web site (www.iseries.ibm.com/db2) that explains this in more detail. Just follow the white paper links off that home page.

micah10277441: I would like to find out how I can use SQL to create queries in any database management system. I know how to do it in Microsoft access, but I want to be able to use it in filemaker pro. filemake pro allows me to import from Microsoft access with SQL statements, but does not allow me to design a query in filemaker pro itself. If there were some way I could enable SQL to be used with this dbms, that would be great. Thanks a million!

Kent_Milligan : Don't know much about that tool, but I'd look and see if it supports ODBC data sources. If so, just create an ODBC data source that uses the Client Access ODBC driver.

topnotchsy48677: Kent, we appreciate the enhancements being made to DB2/400. I can also appreciate that IBM wants to reduce support costs, so it is pushing us to go the SQL route. For many (if not most) shops, this is not practical, for several reasons. This means that we cannot take advantage of those wonderful new features. I would respectfully like to request that those features be enabled in DDS as well.

Kent_Milligan : I understand where you coming from, but we have a limited budget just like your development shop and have to make tough choices. Since most of the software vendors that we work with are using SQL-based interfaces, we'll continue investing most of the R&D money in SQL.

hector_fernandez180488 : Are SQL views better (talking about performance) than Logical files?

Kent_Milligan : The same system object is used for both SQL Views and non-keyed logical files, so they would have similar performance capabilities. However, an SQL view would be able to benefit from DB2 SMP, if needed.

hector_fernandez180488 : I am new to programming an AS/400 and have seen several cases in which an RPG program opens one physical file, and several logical files based on the same physical file. The argument is that the logical files sort the data in as many different ways as needed. But doesn't this method add more overhead than using embedded SQL with ORDER BY clauses only when it is needed?

Kent_Milligan : Most likely SQL would use the same keyed logical files to enforce the ORDER BY specified on your SQL statement. So I'd expect a little better performance with SQL.

cketcher92667: I'd like to use SQL stored procedures, but I can't convince my management to get the C compiler that's required. Does IBM plan to change this?

Kent_Milligan : Yes, that will be included in the 2001 release for iSeries.

davidb376484: When creating procedures do you have to have the ILE C compiler on your machine?

Kent_Milligan : Yes, just for the creation of SQL Stored Procedures. This requirement will be lifted in the next release. Non-SQL stored procedures don't require that compiler, just the compiler for the language that they are written in.

keg137443 : Are there any significant GOTCHAS with moving DB2 files into the IFS? For example issues with packed dec. fields.

Kent_Milligan : Not that I'm aware of, I think the CPYTOSTMF command would handle all of the conversions. Not sure why you would want to do this.

skinnerf331458: Can triggers be set up to run only once (like at the open of a file or the close of a file)?

Kent_Milligan : No, the next version of SQL triggers will support statement level triggers versus row level triggers. But that really wouldn't help out on open/close. The statement level support is more designed for update statements that update multiple rows.

wolffb162890: Is there anything you know of that discusses using scanners with DB2 UDB for OS/400?

Kent_Milligan : No, if you get the scanned file in an IFS file (which could be just a mapped PC directory for the PC attached to the scanner), then you could "Get" it into DB2 with either a BLOB or DATALINK column. More info on these can be found in the DB2 UDB for AS/400 Object Relational Redbook(sg24 5409).

alex_krastoshevsky516355 : Given that SQL is the AS/400 strategic interface, does this mean that OS/400 RPG record level access (READ, READE, CHAIN) will in a way become obsolete?

Kent_Milligan : No it doesn't make it obsolete, it will always be supported. However, you may not be able to use the latest DB2 features via that interface. Your existing programs will continue to run.

skinnerf331458: Does Operations Navigator gain some additional SQL capabilities at V4R5?

Kent_Milligan : Yes, access to DSPFD type of information for Indexes & Tables, Visual Explain, and remembering which libraries you added to your library folder in between sessions. The last one is one of my favorites.

fleetwood910166: In the future, is there a possibility that iSeries applications will be able access remote DB2 or other databases via ODBC data sources?

Kent_Milligan : No, we'll continue to try and utilize the X/Open DRDA standard for heterogeneous data access. If you install IBM DataJoiner on the NT IPCS, DataJoiner can use ODBC to access these data sources on behalf of the iSeries.

dkrentz317113: What concerns should I have using a model 270 (single processor) for a dedicated warehousing application?

Kent_Milligan : A single processor may limit your scalability and I'd also make sure that you have enough memory and disk arms. If an SQL or ODBC based tool is being used, then I'd highly recommend having someone attend the iSeries SQL Performance Workshop (IBM course #S6140). Not many AS/400 shops have this type of performance tuning skill.

keg137443: What kind of "Query Governer" options do I have available to me in V4R5 to control resource utilization of ad hoc queries?

Kent_Milligan : A predictive query governor that allows you to control long-running queries. Currently there are no governors that limit memory or disk usage.

charles.sudjana0390 : Do you know about SQL minus on DB2 UDB for AS/400?

Kent_Milligan : Not yet supported, just have to simulate with existing SQL features (EXCEPTION JOIN)

dellringer661549: What are the top complimentary DB2 OEM products? (I.E. management, query, cube building, etc.)

Kent_Milligan : DB2 QMF for Windows for AS/400 and DB2 OLAP for AS/400.

francis_dagdag369228 : Can you please tell us about the DB2 Multisystem features of DB2?

Kent_Milligan : That feature was primarily designed for customers that outgrow the capabilities of a single iSeries platform. However, with the big HW models available (12 way processors, 40 GB of main store, and over 1.5 TB of disk), we really don't have anyone that needs it. It's similar to DB2 Parallel Edition.

badin48155: Basically, would you recommend that we stop using DDS to create our database and use SQL instead?

Kent_Milligan : I'd definitely look at trying to use it for new tables or a new application to start developing some SQL skills.

themenwa969396: There was a time when embedded SQL for I/O operations was substantially slower than standard I/O . How do they compare now?

Kent_Milligan : There are always going to be things that SQL does faster than native I/O and vice versa. NEWS/400 ran some good comparisons last year that I'd recommend looking at that show it within 10%. Also, we're in the process of re-engineering the database engine for SQL interfaces.

davidb376484: If I was to create a stored procedure that was written in SQL and called from Java, would I still need to wait for the next release if I don't have the C compiler?

Kent_Milligan : Yes.

keg137443: Follow-up to question about moving DB2 files to IFS. We want to move some application interface files to the IFS so they can be encrypted, zipped and FTP'ed to vendors.

Kent_Milligan : Ok, then you might take a look at using CPYTOIMPF as well.

danny.gammon998255 : Is mainframe DB2 UDB, compatible enough with iSeries DB2 UDB to be able to port the same COBOL code with embedded SQL statements between these two platforms.

Kent_Milligan : Could be, really depends on the specific database features that you are using. Supposedly there will be a pub in the next release, which list the detailed differences. The next release will especially eliminate the biggest differences.

dfolland65029: What is the best data access method of using DB2 with Visual Basic?

Kent_Milligan : Via the Client Access ODBC driver and make sure you attend the iSeries SQL Performance Workshop (Course #S6140). I help vendors port to the iSeries--the application design has a huge impact on performance.

kkale326965 : I am considering ASC's SEQUEL. Will I loose out on features that IBM's SQL has that would not be in this package.

Kent_Milligan : Can't say for sure. The only advantage that I'm aware of with their product is they allow SQL to be embedded in CL. You can do a similar thing with RUNSQLSTM (which will be part of the base OS/400 in the next release).

jvoris725677: In Oracle 8i, you can insert Java Methods to "getNode" from XML fields. Other than DAD in DB2 not available for the AS/400, is there any way to do this in DB2/400?

Kent_Milligan : Not sure of that exact interface, but DB2 XML Extenders will be available in the next release of iSeries.

ehurley98085: I see that more and more iSeries DB2 features are available only using SQL, what education is available to help me build some SQL skills?

Kent_Milligan : You can find a listing of education resources (classroom & online) linked off the DB2 UDB for iSeries Web site at: www.iseries.ibm.com/db2/db2educ_m.htm.

skinnerf331458: You have mentioned "2001 release for I-series" several times. Is that a release past V4R5 and will it only be available for iSeries hardware (270,8XX,etc.)?

Kent_Milligan : I don't know the HW requirements. Just a SW guy:)

brian.johnson680165 : An RPG program uses DDM to write many records to a file on a remote AS/400. Would you expect a performance improvement by converting the program to use distributed SQL?

Kent_Milligan : Probably not, but I suppose you could always prototype it.

rnivas4468: Is there anyway we can default field values of table 1 while updating table 1 row with table 2 row field values?

Kent_Milligan : Not without specifying the default value for the columns in table 1 that you want to be defaulted (via DEFAULT keyword or actual default value). If you wanted this for all rows, you could use the INZPFM command to insert default rows into a table.

jnoerr614130: How can we execute multiple SQL statements over an ODBC connection without establishing a new connection each time?

Kent_Milligan : A multi-thread ODBC application would be able to do that.

keg137443: Is the user interface to the predictive query governor the CHGQRYA command?

Kent_Milligan : Yes that's the primary. There's also now a QAQQINI file that allows you to set the time limit as well. You can update the file programmatically or use the graphical interface in Ops Nav.

Moderator: Questions we are unable to get to during the session we will try to answer later and will include them in the transcript.

sjacobs583273: What is the performance comparison of using imbedded SQL vs. using OPNQRYF?

Kent_Milligan : I'd expect the same performance. Plus, you have more power with SQL (eg, CASE expression).

jralston715285: Can you recommend and SQL books?

Kent_Milligan : The SQL/400 Developer's Guide was just put out by the as400network.com and I think can be found on amazon.com You'd want that book to learn AS/400 specifics and then could also use general SQL books available.

tjb138343: Can you recommend books or Websites where I can learn more about basics of using DB2?

Kent_Milligan : I'd recommend the "SQL/400 Developer's Guide" and also "The Database Design & Programming For DB2/400" book by the same author. If you take the education link off the DB2 UDB home page (www.iseries.ibm.com/db2) you'll find education available. The DB2 UDB for AS/400 Fundamentals class would be a good starting point (course #s6145??)

dkrentz317113: Is there any reference information that would aid the transition from a OS/390 DB2 environment to DB2 UDB for the AS/400?

Kent_Milligan : I'm not aware of any public document. If you are a member of PartnerWorld for Developers, we might be able to help you out. Or have your IBM rep contact me.

alex_krastoshevsky516355 : What exactly are the new features (BLOB and ..?) that are supported by SQL and not by DDS?

Kent_Milligan : BLOBs, DATALINKs, User Defined Types, CASE Expression in queries, SOUNDEX function, etc.

mike_dunk636499 : From an Cobol program, why is accessing a file slower using SQL than reading a DDS file?

Kent_Milligan : Depends on the operation. I mentioned earlier there will always be things that native does faster than SQL and that SQL does faster than native. Overtime the native performance advantages will be reduced. Check out the studies done by NEWS/400 last year.

davidb376484: We are on a 720 at V4R4, when you say "2001 iSeries" is this just another OS upgrade? Or is a hardware and software upgrade?

Kent_Milligan : Sorry, I only know about Software. Contact your IBM rep.

kkale326965: Is there a book on SQL you recommend? We are looking at major changes to our database. Can you recommend any books for that?

Kent_Milligan : Look at my previous posts about the SQL/400 Developer's Guide. I'd also strongly suggest attending the iSeries SQL Performance Workshop (S6140) if you want to be successful with your first SQL project.

jnoerr614130: Creating a Store Procedure and executing a RPG program which is called over an ODBC connection, is it necessary to have the "C" complier?

Kent_Milligan : If you've registered an RPG program as a stored procedure with the CREATE PROCEDURE statement, then the C compiler is not required.

davidb376484: What exactly is "Release 2001 iSeries"?

Kent_Milligan : There should be an official announcement in the near future clearing that up:)

blincoln901767: What tools are available/preferred for DB modeling in the iSeries environment?

Kent_Milligan : I'd recommend looking at: PowerDesigner, ERwin, or Popkin's System Architect. You might also contact the ERStudio vendor and ask when their AS/400 version is available.

richard.kish28224 : Vector Indexes - Are you only allowed to create vector indexes using SQL?

Kent_Milligan : Yes, I forgot about SQL Encoded Vector Indexes being one of those SQL-ONLY features. The best place to find out how to use them properly is the iSeries SQL Performance Workshop (course #S6140)

wrknotts193452: Does any table in the system catalog provide field reference information?

Kent_Milligan : Not that I am aware of.

kkale326965: Do you recommend any books on data warehousing?

Kent_Milligan : Ralph Kimball's DW Toolkit is a good one. I'd also look thru the library of AS/400 specific data warehousing books at as400network.com

mudhay156196: Can DB2/ 400 and UDB/400 co-exist on the same AS/400?

Kent_Milligan : DB2/400 & UDB/400 are the same database. The name just changed in V4R4 (with bunches of new function). So if you have an AS/400, you are already using DB2 UDB for AS/400.

richard.kish28224 : Performance - Do you know when the News/400 performance articles were published?

Kent_Milligan : I think it was last year's March issue.

Kent_Milligan : Thanks for your interest--Hope the time was helpful. You can visit the DB2 UDB for AS/400 home page (www.iseries.ibm.com/db2).