Re: SYSCOLUMNS & SYSCOLUMN2

Thanks Bruce - it was an interesting challenge to handle the numerics.

Oh how I wish a few more MI instructions were surfaced as functions. There's just no easy way to get a numeric with the right size and decimals.

On Nov 27, 2019, at 7:12 PM, Bruce Vining <bruce.vining@xxxxxxxxx> wrote:

I have to admit I didn't check out the authorities needed (and didn't
realize I was running with that level of authority as I was on another
persons system). After I confirmed that the buffer length wasn't there I
didn't pursue it any further. I only looked at the QADB* tables in hopes
the SYSCOLUMNS* views were just not externalizing the information, which
was not the case.

With your end goal being an article I can fully understand the route you're
selecting. Have fun!

On Wed, Nov 27, 2019 at 6:46 PM Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:

Yes Jay I would be interested.

Please send it private mail to Jon dot Paris at Partner400 dot com

On Nov 27, 2019, at 6:38 PM, Jay Vaughn <jeffersonvaughn@xxxxxxxxx>

wrote:

Jon. If interested, I’d like to send you a couple of programs I recently

developed you might find some good routine ideas from.

Trigger pgm offloads the filename, trigger buffer before/after string to

data.

Dataq processor pgm uses qdspffd to parse each value from the string and

build a comma delimited string.

We had to do this to work with a 3rd party data replication tool symDS.
The tool slapped on sql generated triggers that performed very poorly on

a few of our larger tables.

Our solution works well.

Jay

Sent from my iPhone

On Nov 26, 2019, at 10:18 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx>

wrote:

I'm trying to build utility routines that can work through a trigger

buffer - or journal image and identify the individual fields that have been
modified.

I used the term table because even if created with DDS it is still a

table that can be accessed with SQL. It's origin is irrelevant to my
purpose.

On Nov 25, 2019, at 11:11 PM, John Yeung <gallium.arsenide@xxxxxxxxx>

wrote:

But it DOES depend on the format. And I don't control that. If the

date was defined as *MDY in the table (yes I know that is foolish but
people do it) then in the program it occupies 8 bytes. If it is *ISO or
*USA then it is 10 bytes.

It sounded like you wanted to look at things from an SQL point of view
(you mentioned "table" rather than "file" for example). From that
point of view, a date is a date is a date. My understanding is that
there isn't such a thing as defining a date format *in the table*,
even though you can do it in a file with DDS.

Of course, I think it would be conceptually possible for IBM to
provide more SQL exposure to DDS-specific features, but so far I don't
think they've done it for this, and I wouldn't expect them to.

Is there another table or a column in either SYSCOLUMNS or

SYSCOLUMN2 that does accurately represent the size of the column or do I
have to calculate it?

I am pretty sure you have to calculate it. But it's not hard. I think
what you are after is simply LENGTH(CHAR(my_date_field)).

Not quite that easy - remember i said it was a utility function - so

the name of the field is not known at compile time.

What does being utility software have to do with anything? One of the
benefits of using SQL is that you have the flexibility to introspect
and compose at run time. That actually strikes me as one of the
primary purposes of SYSCOLUMNS in the first place.

John Y.
--
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@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate link: https://amazon.midrange.com

--
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@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link: https://amazon.midrange.com

--
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@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link: https://amazon.midrange.com

--
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@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
Thanks and Regards,
Bruce
931-505-1915
--
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@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com