Re: Removing keys from Keyed Physical File -- MIDRANGE-L

note that you can prompt DBUJRN and specify the library/table name to use
for the temporary file...

I often do so so that I can then use ACS Run SQL Scripts to query for stuff.

Charles

On Fri, Feb 22, 2019 at 1:54 PM Robert Rogerson <rogersonra@xxxxxxxxx>
wrote:

Buck, Charles, Rob and Gary.

I'm going to take all your answers into consideration and over the
weekend try and resolve the issue with a totally sql solution. Thanks
for all the food for thought.

@Charles, we do have DBU and I tried DBUJRN. Nice, I'll keep that in
mind. I never knew DBU did that. Thanks for the tip.

Thanks everyone,

Rob

On 2/22/2019 3:21 PM, Buck Calabro wrote:

On 2/22/2019 2:46 PM, Robert Rogerson wrote:

Charles, then my understanding of something is incorrect (wouldn't be
the first time).

Here is a date field in one file in DSPFFD

Data Field Buffer Buffer Field
Field Type Length Length Position Usage
SBDATE DATE 10 10 1 Both
Field text . . . . . . . . . . . . . . . : Sales Date
Date Format . . . . . . . . . . . . . . . : *ISO
Default value . . . . . . . . . . . . . . :
'0001-01-01'
Coded Character Set Identifier . . . . . : 37

And if I look at the file in WRKMBRPDM

*...+....1....+....2....+....3....+....4....+....5....+....6....+....7
2018-02-260030607911615300007341002632 0000004000034507122870001-01-01
2018-02-260040607911615300007341002632 0000005000034507122880001-01-01
2018-02-260100607911615300007341002632 0000003000034507122890001-01-01
2018-02-260110607911615300007341002632 0000002000034507122900001-01-01

If I do the same for the file in question...

Data Field Buffer Buffer Field
Field Type Length Length Position Usage
FRDAT DATE 10 10 11 Both
Field text . . . . . . . . . . . . . . . : From Date
Date Format . . . . . . . . . . . . . . . : *USA
Coded Character Set Identifier . . . . . : 37

And if I look at the file in WRKMBRPDM

*...+....1....+....2....+....3
á█|█ä█¤71011/01/201812/31/2022
á█|█ä█¤71411/01/201812/31/2022
á█|█ä█¤71511/01/201812/31/2022

Am I misunderstanding something?

We tend to think that DSPPFM is showing us the 'raw' contents of the
table, but that's not the case. DSPPFM is interpreting the date column
and formatting it nicely for us to understand. So a *YMD gets formatted
differently to a *ISO. In both cases, the underlying (raw) data is in a
form that's ugly for us humans.

With respect to the actual problem, I've learnt the hard way that if I
have an SQL table, I should only manipulate it with SQL and not with CL
commands. You might consider SET OPTION DATFMT, or the scalar function
VARCHAR_FORMAT()

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzscavarcharformat.htm

--
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