EdgardoEhiyan

-- Como leer  un archivo Plano (.txt, CSV..etc) del IFS con SQL
-- y al mismo tiempo convertirlo de un formato de archivo Plano a Tabla
-- By Edgardo Ehiyan

Select REPLACE(SUBSTR(LINE,1,POSSTR(SUBSTR(LINE,1), ';')-1), '"','' )as JOB_NUMBER,
REPLACE(substr(LINE,locate_in_string(LINE, ';', 1, 1) + 1, 
             locate_in_string(LINE, ';', 1, 2) -
             (locate_in_string(LINE, ';', 1, 1)+1)), '"',''  ) as JOB_USE,
REPLACE(substr(LINE,locate_in_string(LINE, ';', 1, 2) + 1, 
             locate_in_string(LINE, ';', 1, 3) -

EdgardoEhiyan

PGM                                                   
RUNSQL     SQL('DROP TABLE MSELE22.LEGAJO') +         
               COMMIT(*NONE)                          
               MONMSG MSGID(SQL9010)                  
RUNSQL     SQL('DROP TABLE MSELE22.NOMBRE') +         
               COMMIT(*NONE)                          
               MONMSG MSGID(SQL9010)                  
RUNSQL     SQL('DROP TABLE MSELE22.PREMIO') +         

EdgardoEhiyan

SELECT ENTRY_TIMESTAMP as JE_TIME, CURRENT_USER as CURRENT_USR,     
SEQUENCE_NUMBER, JOURNAL_ENTRY_TYPE,JOB_NAME,                       
JOB_USER, JOB_NUMBER,PROGRAM_NAME,                                  
OBJECT,OBJECT_TYPE,PATH_NAME,                                       
CAST(cast(substring(entry_data, 1, 32) as char(32) for bit data) as CHAR(32) CCSID 1141) as F1,                                     
CAST(cast(substring(entry_data, 33, 130) as char(130) for bit data) as CHAR(130) CCSID 1141) as F2,                                    
HEX(cast(substring(entry_data, 163, 258) as char(258) for bit data)) as F3,                                                             
HEX(cast(substring(entry_data, 421, 6) as char(6) for bit data)) as N1,                                                                 
HEX(cast(substring(entry_data, 427, 11) as char(11) for bit data)) as N2,                                                              
HEX(cast(substring(entry_data, 438, 4) as char(4) for bit data)) as N