RE: Query packed data?

Wow, there seems to be an endless number of ways to get there. +1



-----Original Message-----
From: Birgitta Hauser [mailto:Hauser@xxxxxxxxxxxxxxx]
Sent: Tuesday, February 27, 2018 12:40 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Subject: RE: Query packed data?

What you can do!

1. Write an External UDTF, i.e. an RPG program that reads the data into an internally described data structure and then returns the data structure subfields.
Here a sample program (it includes even packed data, and also a packed numeric date which is converted into a real date):

****************************************************************************
*******************
* Object name : SPLITFU

* Description : External UDTF - Consuming a program described file
- SPLITF
* Programmer : B. Hauser

* Creation Date : 30.05.2013


****************************************************************************
*******************
// Compilation : Create Program
// CRTBNDRPG PGM(HSUDTF/SPLITFU)
// SRCFILE(HSUDTF/QSRC)
// SRCMBR(SPLITFU)
// REPLACE(*YES)


****************************************************************************
*******************
* H E A D E R S P E C I F I C A T I O N S

****************************************************************************
*******************
/If Defined (*CRTBNDRPG)
H DftActGrp(*No) ActGrp('SPLITFU')
/EndIf
H Debug
H AlwNull(*UsrCtl) ExtBinInt(*YES) Option(*NoDebugIO)

****************************************************************************
******************
* F I L E S P E C I F I C A T I O N S

****************************************************************************
******************
FSplitF IF F 70 Disk UsrOpn

****************************************************************************
********************
* P R O T O T Y P I N G

****************************************************************************
********************
/Include QSRC,CallType

// Procedure Interface
D SPLITFU PI

// Input Parameters

// Output Parameters - Return Table Columns
D POutFirstName 50A Varying
First name
D POutName 50A Varying
Last Name
D POutZipCode 5P 0
Zip code
D POutCity 50A Varying
City
D POutBirthDay D DatFmt(*ISO)
Birth day

// Null Indicators for Input Parameters

// Null Indicators for Return Table Columns
D POutFirstNameInd...
Indicator First Name
D 5I 0
D POutNameInd 5I 0
Indicator Last Name
D POutZipCodeInd...
D 5I 0
Indicator Zip code
D POutCityInd 5I 0
D POutBirthDayInd...
Indicator Birthday
D 5I 0

// SQL Function Parameters
d POutSQLState 5A
SQLSTATE
d ParFunction 517A varying Const
Fully Qualified Function name
d ParSpecName 128A varying Const
Specific Schema
d POutMsgText 1000A varying
Message Text
d ParCallType 10I 0
Call Type

****************************************************************************
******************
* G L O B A L V A R I A B L E D E F I N I T I O N S

****************************************************************************
******************
* Data structure for receiving the program described file
D DSSplitF DS 70 Qualified Inz
D FirstName 15A
First Name
D Name 15A
Last Name
D ZipCode 5P 0
Zip Code
D City 15A
City
D BirthDay 8P 0
Birthday

D PGMSDS SDS
D SDSMsgTxt 91 170
Message-Text

****************************************************************************
********************
* M A I N P R O G R A M

****************************************************************************
********************
Monitor;
//Initialize Output Parameters
POutSQLState = *Zeros;
//SQLSTATE
Clear POutMsgText;
//Message Text
clear POutFirstName;
//First Name
clear POutName;
//Last Name
clear POutZipCode;
//Zip code
clear POutCity;
//City
clear POutBirthDay;
//Birthday
clear POutFirstNameInd;
//Indicator First Name
clear POutNameInd;
//Indicator Last Name
clear POutZipCodeInd;
//Indicator Zip code
clear POutCityInd;
//Indicator City
clear POutBirthDayInd;
//Indicator Birthday

//1. Open Call --> Open program defined file
If ParCallType = ConstOpenCall;
If %Open(SplitF);
Close SplitF;
EndIf;
Open SplitF;
SetLL *Start SplitF;
Return;
EndIf;

//2. Fetch Calls --> Read next record from program defined file
if ParCallType = ConstFetchCall;
Read SplitF DSSplitF;
If Not %EOF(SplitF);
POutFirstName = %Trim(DSSplitF.FirstName);
POutName = %Trim(DSSplitF.Name);
POutZipCode = DSSplitF.ZipCode;
POutCity = %Trim(DSSplitF.City);
Monitor;
POutBirthDay = %Date(DSSplitF.BirthDay: *ISO);
On-Error;
Clear POutBirthDay;
EndMon;
EndIf;
EndIf;

//3. Close Call --> Close File / End Program
If ParCallType = ConstCloseCall or %EOF(SplitF);
If %Open(SplitF);
Close SplitF;
EndIf;
POutSQLState = '02000';
POutMsgText = 'End Of File';
EndIf;

On-Error;
POutSQLState = '38X11';
POutMsgText = SDSMsgTxt;
EndMon;

Return;



2. Program is registered as UDTF (user defined table function).
Here is the registration as UDTF
Create Function SPLITF_Fnc ( )
Returns Table (ParFirstName VarChar(50) ,
ParName VarChar(50) ,
ParZipCode Dec(5, 0),
ParCity VarChar(50) ,
ParBirthDay Date)
Language RPGLE
Not Deterministic
No SQL
Called On NULL Input
No External Action
Disallow Parallel
Not Fenced
External Name 'HSUDTF/SPLITFU'
Parameter Style DB2SQL ;

Comment On Specific Function SPLITF_Fnc
is 'Read program described file - SPLITF' ;




3. Now it can be queried from any SQL interface.
Here is the SQL Execution:

Select * from Table(HSUDTF.SPLITF_Fnc()) x; Now you can add WHERE Conditions, GROUP BY, ORDER BY, in short you can handle the UDTF like any Table or View

It is also possible to create a view over the UDTF:

Create View YOURSCHEMA.YOURVIEW
As (Select * from Table(HSUDTF.SPLITF_Fnc()) x);

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Justin Taylor
Sent: Montag, 26. Februar 2018 19:58
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Query packed data?

Specifying an empty externally-described PF and OVRDBF to the program-described file, now that's getting creative. +1



-----Original Message-----
From: Mark S Waterbury [mailto:mark.s.waterbury@xxxxxxxxxxxxx]
Sent: Monday, February 26, 2018 12:32 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Query packed data?


My bad ...

SST in DDS does not support "packed decimal" fields ...

You could also create a physical file with DDS, with a record layout
"matching" the fields in the "flat file" with LVLCHK(*NO) and then OVRDBF
that file "over" the existing "flat file" .. .

Or, you could use CPYF ... FMTOPT(*NOCHK) to copy the data (temporarily)
into your DDS-defined PF with the "correct" record layout (no need for
LVLCHK(*NO) in that case?) ... Then you would have "packed fields"
defined in all the right places ... and you can query or report against that
"temporary" file as needed ...

Of course, if there is data that is not valid "packed decimal" data in the
flat file, well ... good luck ...

HTH,

Mark S. Waterbury