|
DB2 Example: GET Table Size
Here's an example of a User Defined Function (UDF) implemented with
an external C program that uses OS/400 database API's to return the
size of a database table in bytes.
Here's the CREATE FUNCTION statement to register the C program
as a UDF:
CREATE FUNCTION
GetTableSize(TableandLib VARCHAR(20))
RETURNS DECIMAL(15,0)
EXTERNAL NAME 'mylib/getsize(getsize)'
LANGUAGE C PARAMETER STYLE SQL;
Sample invocation:
SELECT table_schema, table_name,
GetTableSize(system_table_name CONCAT system_table_schema) AS Size
FROM qsys2.systables
WHERE table_schema = 'mylibrary'
ORDER BY 3;
Source code for the external C program:
/*=======================================================*/
/* GetTableSize UDF */
/* INPUT - VARCHAR(20) */
/* Concatenation of table & library name */
/* OUTPUT - DECIMAL(15,0) */
/* Size of table bytes */
/* */
/* */
/*COMPILE STEPS: */
/* The includes in the QSYSINC library must be on */
/* your system to compile this program. Option 13 of */
/* the OS/400 install will install them. Also */
/* SYSINC(*YES) (the default) must be specified on */
/* CRTBNDC or CRTCMOD. */
/* */
/* CRTCMOD MODULE(mylib/getsize) */
/* SRCFILE(mysrc/qcsrc) OUTPUT(*print) */
/* OPTION(*showinc) DBGVIEW(*all) */
/* */
/* CRTSRVPGM SRVPGM(mylib/getsize) */
/* MODULE(mylib/getsize) */
/* EXPORT(*all) */
/* ACTGRP(*caller) */
/* */
/*=======================================================*/
void GETSIZE(char *FileArg,
char *FileSize,
short *inind,
short *outind,
char *sqlstate,
char *funcname,
char *specname,
char *msgtext)
{
#include
#include
#include
#include
/*=======================================================*/
/* */
/* Include the Error Code Structure */
/* */
/*=======================================================*/
#include
Qus_EC_t *errstr;
/*=======================================================*/
/* */
/* Include the SQLUDF Structures */
/* */
/*=======================================================*/
#include
/*=======================================================*/
/* */
/* Include the QUSRJOBI Structures */
/* */
/*=======================================================*/
#include
Qus_OBJD0400_t Rfd_Input;
Qus_OBJD0400_t *fdthdr;
char File[21]; /* Table / Library name */
char Type[11]; /* Object type */
char Receiver_Var[8000]; /* Area to receive the output */
/* from QUSROBJD */
char Format_Var[9]; /* QUSROBJD format name */
char Error_Code[201]; /* Error area from QUSROBJD */
/* Set addressability to the return template */
errstr = (Qus_EC_t *) &Error_Code[0];
errstr->Bytes_Provided = sizeof(Error_Code)-1;
/* Set up parameters for QUSROBJD API */
memcpy( Type, "*FILE ", 10);
/* Set Object Type */
memcpy( File, FileArg, 20);
/* Set Object Name and Library */
Rfd_Input.Bytes_Available = 8000;
/* Set bytes available */
memcpy( Format_Var, "OBJD0400", 8);
/* Set the output format */
/* Set addressability to the return template */
fdthdr = (Qus_OBJD0400_t *) &Receiver_Var[0];
/* We can always get all the data on one call */
QUSROBJD((Qus_OBJD0400_t *) fdthdr,
Rfd_Input.Bytes_Available,
Format_Var,
File,
Type,
Error_Code); /* Call the QUSRJOBI API */
/* If an error was not returned then set the output */
/* parameters */
if (errstr->Bytes_Available == 0) /* An error did not occur */
{
* (decimal(15) *) FileSize = fdthdr->Object_Size *
fdthdr->Object_Size_Multiplier;
/* Set the object Size */
*outind = 0; /* Set the parameter null ind */
memcpy( sqlstate, "00000", 5);
/* Set the SQLSTATE */
}
else
{
* (decimal(15) *) FileSize = 0;
/* Set the object Size */
*outind = -1; /* Set the parameter null ind */
memcpy( sqlstate, "00000", 5);
/* Set the SQLSTATE */
}
return;
}
| 
