Re: Removing keys from Keyed Physical File

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