EdgardoEhiyan

EdgardoEhiyan / SQL_CSV.txt

Created August 27, 2021 13:19

	-- 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 / gist:4a40ee7084674bcbba21ed4283cbe340

Created August 6, 2020 19:33

Convierte archivo plano de un solo campo a una Tabla SQL con formato (DB2 for i ) Usando CL con SQL embebido



	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 / gist:da94c085296776a297636d240222d855

Created July 1, 2020 13:52

Statment SQL QSYS2.DISPLAY_JOURNAL ( for Object /Date/ and recivers(start-end)

	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