Scalable Database Server, HiRDB Version 8 Command Reference

[Contents][Index][Back][Next]

8.10.5 Example of a UOC

This section presents an example of table reorganization and the table unload operation using a UOC.

The example of UOC coding shown here is provided as a sample database. It is stored in the directory $PDDIR/sample/sampleUOC.

Organization of this subsection
(1) Example of table reorganization using a UOC
(2) Example of table unload operation using a UOC

(1) Example of table reorganization using a UOC

During table reorganization, this example deletes data whose database registration date is year 2001 or earlier.

(a) Database table definition
 
  CREATE TABLE MEMBVER_DIRECTORY (MEMBER_NUMBER INTEGER,
                     MEMBER_NAME NCHAR(20),
                     MEMBER_ADDRESS NVARCHAR(100),
                     JOINED_DATE_AND_TIME DATE NOT NULL WITH DEFAULT);
(b) Command format
 
 pdrorg -k rorg -t MEMBVER_DIRECTORY control-information-file
(c) Contents of control information file
 
 unload unload-data-file-name uoc_lib=library-name
 unlduoc entry=old_data_delete
(d) Example of UOC coding
 
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <errno.h>
#include <pdutluoc.h>   ..........................................................1
 
void old_data_delete (
    UTL_UOC_INF         *uocinf             /* A(UOC interface area)      */   ...2
) {
    UTL_UOC_DATA_BUF    *addr_list;         /* A(data address list)       */
    unsigned char       *date_adr;          /* A(DATE)                    */
    static unsigned char cyear[2];          /* compare year               */
 
    switch(uocinf->req_cd){   ....................................................3
    case UTL_UOC_START:
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
/*  START                                                                 */
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
        cyear[0] = 0x20;                    /* The 21st century           */
        cyear[1] = 0x01;                    /* (2001)                     */
        uocinf->rtn_code = UTL_UOC_NML;
        break;
    case UTL_UOC_EDIT:
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
/*  EDIT                                                                  */
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
        addr_list = uocinf->data_adr;
        date_adr = addr_list->data[3];
        if (memcmp(date_adr,cyear,sizeof(cyear)) < 0){
            uocinf->unload_flg = UTL_UOC_ROWNOPUT;
        }else{
            uocinf->unload_flg = UTL_UOC_ROWPUT;
        }   ......................................................................4
        uocinf->rtn_code = UTL_UOC_NML;
        break;
    case UTL_UOC_END:
    case UTL_UOC_TERM:
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
/*  END                                                                 */
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
        uocinf->rtn_code = UTL_UOC_NML;
        break;
    default:
        strcpy(uocinf->err_msg,"Invalid request code");
        uocinf->rtn_code = UTL_UOC_ERR;
    }
    return;
}

Explanation:
  1. Includes the header for UOC creation that is provided by HiRDB.
  2. Receives the address of the UOC interface area as an argument.
  3. Checks the call type and executes appropriate processing.
  4. Checks the data (date) and sets the storage flag.

(2) Example of table unload operation using a UOC

This example edits BLOB data in CSV format when a table is unloaded. The example creates UOC data files based on one data item per file.

(a) Database table definition
 
  CREATE TABLE IMAGE_TBL
           (COL1 INTEGER NOT NULL,
            COL2 CHAR(20) NOT NULL,
            COL3 INTEGER NOT NULL,
            COL4 CHAR(5) NOT NULL,
            COL5 BLOB(100K) IN (LOB_AREA) NOT NULL)
           IN DATA_AREA;
(b) Command format
  
 pdrorg -k unld -j -t IMAGE_TBL control-information-file
(c) Contents of control information file
  
 unload (uoc) uoc_lib=library-name param='/usr/tmp/lob,/tmp/uoc_file'
 unlduoc entry=blob_file_create
(d) Example of UOC coding
 
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <errno.h>
#include <fcntl.h>
#include <pdutluoc.h>
 
#define     INT         0xf0                /* INTEGER                   */
#define     CHAR        0xc4                /* CHAR                      */
#define     BLOB        0x92                /* BLOB                      */
 
void blob_file_create (
    UTL_UOC_INF         *uocinf             /* A(UOC interface area)     */
) {
    static  char         token[] = ",";     /* parameter token           */
    static  char         prefix[40];        /* path name prefix          */
    static  char         filename[80];      /* CSV file name             */
    static  char        *buff = NULL;       /* row edit buffer           */
    static  int          counter = 0;       /* row counter               */
    static  int          file_id = 0;       /* CSV file file ID          */
    int                  lobfid;            /* BLOB file                 */
    int                  leng;              /* total length              */
    int                  int_len;           /* INTEGER length            */
    int                  i;                 /* counter for column        */
    int                  rc;                /* return code               */
    int                 *nagasa;            /* BLOB size                 */
    void                *blobadr;           /* A(BLOB data)              */
    char                *wkadr;             /* work address              */
    UTL_UOC_DATA_BUF    *addr_list;         /* A(data address list)      */
    UTL_UOC_COLUMN_INFO *col_inf;           /* A(column information)     */
    char                *sepachr = ",";     /* separator character       */
    char                *linechr = "\n";    /* line field character      */
    char                 dmyname[16];       /* temporary name            */
    char                 blobfile[128];     /* BLOB file name            */
 
    switch(uocinf->req_cd){
    case UTL_UOC_START:
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]-*/
/*  START                                                                */
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]-*/
        if (uocinf->user_param == NULL){
            strcpy(uocinf->err_msg,"Required parameter not specified");
            goto OWARI;
        } 
 
       wkadr = strtok(uocinf->user_param,token);
        if ((wkadr == NULL) ||
            (strlen(wkadr) >= sizeof(prefix))){
            strcpy(uocinf->err_msg,"Invalid parameter");
            goto OWARI;
        }
        strcpy(prefix,wkadr);   ...................................................1
        wkadr = strtok(NULL,token);
        if ((wkadr == NULL) ||
            (strlen(wkadr) >= sizeof(filename))){
            strcpy(uocinf->err_msg,"Invalid parameter");
            goto OWARI;
        }
        strcpy(filename,wkadr);   .................................................1
 
        file_id = create(filename,0666);
        if (file_id == -1){
            strcpy(uocinf->err_msg,strerror(errno));
            goto OWARI;
        }
        buff = malloc(1024);
        if (buff == NULL){
            strcpy(uocinf->err_msg,strerror(errno));
            goto OWARI;
        }
 
        counter = 0;
        strcpy(uocinf->err_msg,"FILE NAME:");
        strcat(uocinf->err_msg,filename);
        uocinf->rtn_code = UTL_UOC_DBG;
        break;
    case UTL_UOC_EDIT:
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
/*  EDIT                                                                */
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
        counter++;
        col_inf = uocinf->dic_inf;
        addr_list = uocinf->data_adr;
        leng = 0;
        wkadr = buff;
        for(i = 0; i < uocinf->col_num; i++){
            switch(col_inf->colinf[i]->clm_type){
            case INT:   ..........................................................2
                int_len = sprintf(wkadr,"%d",*((int*)addr_list->data[i]));
                leng += int_len;
                wkadr += int_len;
                break;
            case CHAR:   .........................................................2
                strncpy(wkadr,addr_list->data[i],col_inf->colinf[i]->clm_len);
                leng += col_inf->colinf[i]->clm_len;
                wkadr += col_inf->colinf[i]->clm_len;
                break; 
 
            case BLOB:   .........................................................3
                sprintf(dmyname,"%010d",counter);
                strcpy(blobfile,prefix);
                strcat(blobfile,dmyname);
                lobfid = create(blobfile,0666);
                if (lobfid == -1){
                    strcpy(uocinf->err_msg,strerror(errno));
                    goto OWARI;
                }
                nagasa = (int *)((int)(addr_list->data[i]) + sizeof(int));
                blobadr = (void *)((int)(addr_list->data[i]) +
                                                        (sizeof(int) * 2));
                rc = write(lobfid,blobadr,*nagasa);
                if (rc != *nagasa){
                    strcpy(uocinf->err_msg,strerror(errno));
                    goto OWARI;
                }
                rc = close(lobfid);
                if (rc == -1){
                    strcpy(uocinf->err_msg,strerror(errno));
                    goto OWARI;
                }
                strcpy(wkadr,blobfile);
                leng += strlen(blobfile);
                wkadr += strlen(blobfile);
                break;
            default:
                strcpy(uocinf->err_msg,"Not support data type");
                goto OWARI;
            }
            strcpy(wkadr,sepachr);
            leng += 1;
            wkadr++;
        }
        wkadr[Figure];
        strcpy(wkadr,linechr);
 
        rc = write(file_id,buff,leng);
        if (rc != leng){
            strcpy(uocinf->err_msg,strerror(errno));
            goto OWARI;
        }
        uocinf->rtn_code = UTL_UOC_NML;
        break; 
 
    case UTL_UOC_END:
    case UTL_UOC_TERM:
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
/*  END                                                                 */
/*[Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure] [Figure]*/
        if (buff != NULL){
            free(buff);
            buff = NULL;
        }
        if (file_id != 0){
            rc = close(file_id);
            file_id = 0;
            if (rc == -1){
                strcpy(uocinf->err_msg,strerror(errno));
                goto OWARI;
            }
        }
        uocinf->rtn_code = UTL_UOC_NML;
        break;
    default:
        strcpy(uocinf->err_msg,"Invalid request code");
        goto OWARI;
    }
    return;
 
OWARI:
    uocinf->rtn_code = UTL_UOC_ERR;
    return;
}

Explanation:
  1. Expands the user parameters specified in the control information file into variables within the UOC.
  2. Outputs the data for an INTEGER or CHAR column in characters to a UOC data file.
  3. Saves the data for a BLOB column in rows to a file and then outputs the file name to a UOC data file.

[Contents][Back][Next]

[Trademarks]

All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.