SQL /data area question

Hal

unread,

Jun 8, 2009, 5:09:22 PM6/8/09

to

All,

I am in the process of writing some SQL queries to pull data out of
some unfriendly tables and re-org things into a much nicer format.
That parts works fine. But many records I am trying to reorganize are
year-specific, and further, the fiscal year on the record does not
follow the calendar year.

Anyway..in a data area on the 400 there is a field called "MYEAR".
Right now it is 2009. In my CL program that successively runs a bunch
of SQL statements(see above) I need to be able to reference the year
as specified in data area value MYEAR as part of my where clause. How
can I do that? I wanted to send that piece of information in when I
issue the 'RUNSQLSTM' command but see no way to do that. Then I
thought maybe I could take that value from MYEAR, assign it to a CL
variable, and then insert that value into a table somewhere so that
each time I run the CL, it updates whatever value is MYEAR so I always
have the current year. I can't figure a way to do that, either.

I'm stumped. Any ideas?

Chris

Thad Rizzi

unread,

Jun 8, 2009, 5:40:12 PM6/8/09

to

I've done something similar to that in an RPGLE program using embedded
SQL. I retrived data from a data area and assinged it to a variable.
I then built a text string for my SQL statement using that variable
(and others). Then I used the PREPARE statement to put it all
together.

Thad Rizzi

dieter...@t-online.de

unread,

Jun 9, 2009, 2:43:32 AM6/9/09

to

put it in a table and all your problems are gone

Dieter

dieter...@t-online.de

unread,

Jun 9, 2009, 4:38:02 AM6/9/09

to

STRQMQRY will do this, it accepts parameter for hostvars

Karl Hanson

unread,

Jun 9, 2009, 9:06:31 AM6/9/09

to

One thought - create a SQL function (aka user-defined function, or UDF)
that retrieves the data area and returns it. This could be referenced
in the WHERE clause. For example a CL program could use RTVDTAARA
command, and SQL CREATE FUNCTION would register the CL program, eg using
LANGUAGE CL and EXTERNAL NAME clauses.

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/db2/rbafzcfsce.htm
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/sqlp/rbafywudfextern.htm

--
Karl Hanson

Hal

unread,

Jun 11, 2009, 11:21:02 PM6/11/09

to

On Jun 9, 1:38 am, dieter.ben...@t-online.de wrote:
> STRQMQRY will do this, it accepts parameter for hostvars
>
> Then I
>

That's how I ended up making it work. I was getting some bad
information about how to embed a variable name into a query manager
query. I have a CL wrapper program that I run a couple times a day via
the job scheduler. The CL defines a variable, retrieves the value I
want from the data area, puts the value into said variable, and then I
do the strqmqry and pass the variable in and whala....problem solved.
My value is now in a database table. Then I went a step further, since
the table I put that value into only has one row, and one column, and
created an SQL function so I can reference that year value in a
standard SQL statement without having to go through the whole "join
table such and such on so and so".

Works like a champ. I love DB2.

Chris