Re: Creating SQL Stored Procedures and Trigger programs without DB2Query Manager and SQL Development Kit...

On Mon, Sep 21, 2009 at 4:54 PM, <rob@xxxxxxxxx> wrote:

The SQL precompiler is not meant to be a learning tool as to how to use
SQL CLI.  It uses an entirely different technique.

right. It apparantly uses a call to a system program named SQLROUTE.
SQL CLI runs your SQL statement in a spawned job in a separate
subsystem. It is sluggish compared to SQLROUTE which runs inline.

You lose nothing by not being able to retrieve exactly what is done with
the precompiler.  There are things that the SQL CLI does that the
precompiler cannot but there is nothing in the reverse (IMHO).  And I've
never used SQL CLI.

The value add of SQL CLI is it allows your code to call an sql
procedure and get back the result set data. Effectively, SQL CLI is
ODBC from the local system.

IBM does not prevent the customers from rolling their own.
They do not hide it.  SQL CLI is documented.
And I'm not sure if the associated program space is a limiting factor in
any way.

So where are the 3rd party precompilers which compete with the SQL
development kit? It would be great to be able to write a CL pre
compiler which enabled a mix of CL, SQL and RPG free style service
program procedure calls. All the pieces are there in terms of the
debugger view and pre compiler APIs. Problem is SQL CLI runs in
another job ( cumbersome ), SQLROUTE is not documented and the
QbnAddAssociatedSpaceEntry API ( presumably used to store the sql stmt
text that SQLROUTE works with ) does not work.

Believe me, I am not being argumentative. In the interest of learning
something, I would be glad to be proved wrong with anything I am
posting here. Based on what I know technically, this part of the
system ( the pre compiler and access to SQLROUTE ) was left incomplete
( or crippled ) years ago to spur the sales of the SQL dev kit.
Nowadays, even if our system p overlords wanted to correct and improve
things, there is likely no one left on staff with the knowledge to do
the job.