Submitted by Syd Nicholson on Fri, 05/16/2008 - 00:00
Forums
IceBreak

I am trying to keep all source code in the IFS. However I need to create database files and would like to use SQL definition language to do this.

Using a traditional approach one would place the source in a source file member and use the RUNSQLSTM command (or equivalent) to create the database table. - but, this does not use the IFS

There does not appear to any obvious way to run SQL statements stored in an IFS file.

Does anybody know if IceBreak has the biult in function to allow SQL source code to be stored in the IFS and yet still be able to execute this code on the i5.

Thanks in advance
Syd

Hi,

have you ever tried to use iSeries Navigator Database?
With Run an SQL Script, your can store your "source code" in the IFS. After reopening the source code it is possible to reexecute it.

With reverse engineering the source code from any database object can be generated and stored in either a traditional source file member or in an file with extender .sql that can be stored in either IFS or on your PC.

With release V6R1 the CL-Command RUNSQLSTM is enhanced to run on IFS files.

Birgitta

Syd,

the way I do it is to create an IceBreak setup or installation program that can check various settings and use embedded SQL to create a collection, then tables, views etc. and maybe even insert test/demo or default rows in the tables. It's quite easy and all you need to do is to use:

 
 <@ language="SQLRPGLE" options="........" %>

 instead of just:

 
 <@ language="RPGLE" options="........" %>

 then in you main or subroutine you use:

 
  /exec sql CREATE COLLECTION MyLib;

  /exec sql CREATE TABLE MyLib/MyTable1 .....;

etc.

If you create it so that it can take different parameters you will be able to use the same program for inserting or checking for default rows later on, which the user or someone else might have removed.

You can even store your default SQL's in an XML file that you distribute with the program, or download the SQL's from your website... this way you can distribute your program and make sure that when people install it or use it for the first time, the up-to-date SQL's are executed.... or maybe even "repaired".

You might even hardcode some variables like ${LibraryName} inside your SQL and ask the user for the library name before replacing this constant with the selected library and then execute SQL that has the correct library. I'm sure you can think of even more possibilities like this....

Thanks John,

I use the 'language="SQLRPGLE"' approach regularly. I prefer SQL to DDS when appropriate.

I was looking for something that not involve much programming and the Ops Nav solution suggested by Birgitta is ideal. Using this approach I can create the SQL DDL source in the IFS and use Ops Nav to execute it, and map the database.

It is shame Ops Nav does not run in Linux though. Oh well -- back to Windows!!

Regards
Syd