midrange.com code scratchpad
Name:
Segregate_By_Year
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
04/25/2022 08:08:54 pm
IP:
Logged
Description:
Busts huge directory into directories for year the object was created.
Code:
  1. SET schema = 'ROB';
  2.  
  3. drop procedure Segregate_By_Year;
  4.  
  5.  
  6. CREATE PROCEDURE Segregate_By_Year
  7.          (IN START_PATH VARCHAR(50) )
  8.          LANGUAGE SQL MODIFIES SQL DATA
  9.          SPECIFIC SEG_BY_YR
  10.         SET OPTION DATFMT = *ISO, DBGVIEW = *SOURCE
  11.          P1: BEGIN
  12.           DECLARE WORK_PATH_NAME VARCHAR(500);
  13.           DECLARE WORK_YEAR INT;
  14.           DECLARE BREAK_YEAR INT;
  15.           DECLARE OBJECT_COUNT INT;
  16.           DECLARE COMMAND CHAR(500);  -- Ensure this is large enough.
  17.           DECLARE END_TABLE INT DEFAULT 0;
  18.           DECLARE C1 CURSOR FOR
  19.             select int(year(CREATE_TIMESTAMP)) as create_year, path_name
  20.             from table(qsys2.IFS_OBJECT_STATISTICS(
  21.             start_path_name => START_PATH,
  22.             OBJECT_TYPE_LIST => '*ALLSTMF',
  23.             subtree_directories => 'NO'))
  24.             order by 1 asc
  25.             ;
  26.  
  27.           DECLARE CONTINUE HANDLER FOR NOT FOUND
  28.             SET END_TABLE = 1;
  29.         --  DECLARE EXIT HANDLER FqsysOR SQLEXCEPTION
  30.         --    SET DEPT_SALARY = NULL;
  31.           OPEN C1;
  32.           SET WORK_YEAR=0;
  33.           SET BREAK_YEAR=0;
  34.           FETCH C1 INTO WORK_YEAR, WORK_PATH_NAME;
  35.           WHILE END_TABLE = 0 DO
  36.              -- MOV OBJ('/home/ROB/yadda') TODIR('/home/ROB/1998')
  37.              IF WORK_YEAR > BREAK_YEAR then
  38.                 -- If the year subdirectory doesn't exist, create it
  39.                 SELECT COUNT(*) into OBJECT_COUNT
  40.                 from table(qsys2.IFS_OBJECT_STATISTICS(
  41.                 start_path_name => START_PATH concat '/' concat trim(CHAR(WORK_YEAR)),
  42.                 subtree_directories => 'NO'));
  43.                 IF OBJECT_COUNT = 0 then
  44.                     SET COMMAND = 'MD DIR(''' concat START_PATH concat '/' concat trim(CHAR(WORK_YEAR)) concat ''')';
  45.                     CALL QSYS2.QCMDEXC(COMMAND);
  46.                 END IF;
  47.                 SET BREAK_YEAR = WORK_YEAR;
  48.              END IF; -- Control break on year    
  49.              SET COMMAND = 'MOV OBJ(''' concat work_path_name concat ''') TODIR(''' concat START_PATH concat '/' concat trim(CHAR(BREAK_YEAR)) concat ''')';
  50.              CALL QSYS2.QCMDEXC(COMMAND);
  51.              FETCH C1 INTO WORK_YEAR, WORK_PATH_NAME;
  52.            END WHILE;
  53.            CLOSE C1;
  54.          END P1          
  55. ;
  56. CALL ROB.Segregate_By_Year('/home/ROB/test');
© 2004-2019 by midrange.com generated in 0.005s valid xhtml & css