When you remove the OMIT specs from the joins do you get data?
When you include the OMIT specs you are asking the database to return NULLs
in xxx_IST fields.
Wild ass guess:
Do you need to specify ALLNULL on the fields in the record layout?
Paul
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Patrik
Schindler
Sent: Sunday, August 22, 2021 9:03 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Implement SQL in logical files
Hello,
Maybe some of you can remember this topic from a while ago.
Introduction
============
I'm still completing a collection of helper applications for easier tracking
of Cisco OS updates, for all the devices in a VPN to friends and relatives.
To correctly handle this, I need to manually keep track of all Cisco devices
and their recommended OS release, in a table called osmatchpf. Also, a cron
job on a separate Linux instance starts a perl script logging into all
devices, extracting inventory information, and pushing that into dcapf
("Data Collected Automatically") via ODBC.
I've developed the base functionality years ago in MySQL on Linux and
"ported" it to DB2 on OS/400 somewhen. Ported, because the SQL syntax is
considerably different. There were (and are) two SQLs generating reports of
entries being either missing or orphaned in osmatchpf, when considering the
content of dcapf.
This report functionality is put into a SQLRPGLE and DSPF pair, so one
easily could do housekeeping (deleting orphans, copy missing entries into
osmatchpf).
Everything's working fine until here.
Goal
====
So far, the SQL version does its job, but it takes "too long" for my
personal taste until the screen is displayed: Everything else (not using
SQL) is nicely quick. Thus, I want to "convert" the SQL statements into the
functions being available in logical files: Joins, and Select/Omit.
Problem is: I can't get it to work. The result is always empty. I'm doing
something wrong, but I can't see what. Help is greatly appreciated!
Make it easy for you to help me
===============================
I have prepared a collection of files here:
https://leela.pocnet.net/~poc/sql-to-lf-examples.zip
The ZIP contains PDF'd printout of DDS source from all PFs and LFs, and two
screen shots. One is showing actual output of an interactive SQL session
over one LF, (temporarily) without S/O statements; to show database content
as helpful example. The other is showing the report screen as outlined above
with the correct divergences in the tables.
If anyone needs the data files themselves for easier testing, let me know in
a private message. I'll prepare a *SAVF of osmatchpf and dcapf then.
Disclaimer
==========
Allow me to emphasize that I'm using a slow but power savvy 150 in a solely
hobbyist environment. Having used an 800 heavily for one week, I can truly
state, that it's fast enough for what I'm doing.
I plan to release the said helper applications on GitHub at some later point
in time, when they are feature complete: including a proper help system, and
translated from German (my native language) into English.
Thank you!
:wq! PoC
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
