Code:
- SET schema = 'ROB';
-
- drop procedure Segregate_By_Year;
-
-
- CREATE PROCEDURE Segregate_By_Year
- (IN START_PATH VARCHAR(50) )
- LANGUAGE SQL MODIFIES SQL DATA
- SPECIFIC SEG_BY_YR
- SET OPTION DATFMT = *ISO, DBGVIEW = *SOURCE
- P1: BEGIN
- DECLARE WORK_PATH_NAME VARCHAR(500);
- DECLARE WORK_YEAR INT;
- DECLARE BREAK_YEAR INT;
- DECLARE OBJECT_COUNT INT;
- DECLARE COMMAND CHAR(500); -- Ensure this is large enough.
- DECLARE END_TABLE INT DEFAULT 0;
- DECLARE C1 CURSOR FOR
- select int(year(CREATE_TIMESTAMP)) as create_year, path_name
- from table(qsys2.IFS_OBJECT_STATISTICS(
- start_path_name => START_PATH,
- OBJECT_TYPE_LIST => '*ALLSTMF',
- subtree_directories => 'NO'))
- order by 1 asc
- ;
-
- DECLARE CONTINUE HANDLER FOR NOT FOUND
- SET END_TABLE = 1;
- -- DECLARE EXIT HANDLER FqsysOR SQLEXCEPTION
- -- SET DEPT_SALARY = NULL;
- OPEN C1;
- SET WORK_YEAR=0;
- SET BREAK_YEAR=0;
- FETCH C1 INTO WORK_YEAR, WORK_PATH_NAME;
- WHILE END_TABLE = 0 DO
- -- MOV OBJ('/home/ROB/yadda') TODIR('/home/ROB/1998')
- IF WORK_YEAR > BREAK_YEAR then
- -- If the year subdirectory doesn't exist, create it
- SELECT COUNT(*) into OBJECT_COUNT
- from table(qsys2.IFS_OBJECT_STATISTICS(
- start_path_name => START_PATH concat '/' concat trim(CHAR(WORK_YEAR)),
- subtree_directories => 'NO'));
- IF OBJECT_COUNT = 0 then
- SET COMMAND = 'MD DIR(''' concat START_PATH concat '/' concat trim(CHAR(WORK_YEAR)) concat ''')';
- CALL QSYS2.QCMDEXC(COMMAND);
- END IF;
- SET BREAK_YEAR = WORK_YEAR;
- END IF; -- Control break on year
- SET COMMAND = 'MOV OBJ(''' concat work_path_name concat ''') TODIR(''' concat START_PATH concat '/' concat trim(CHAR(BREAK_YEAR)) concat ''')';
- CALL QSYS2.QCMDEXC(COMMAND);
- FETCH C1 INTO WORK_YEAR, WORK_PATH_NAME;
- END WHILE;
- CLOSE C1;
- END P1
- ;
- CALL ROB.Segregate_By_Year('/home/ROB/test');
|
|