CREATE
OR
REPLACE
PROCEDURE
importData(
IN
importPathAndFileName
varchar
(100),
IN
tabSchema
varchar
(100),
IN
tabName
varchar
(100),
IN
exportMSGPath
varchar
(100))
LANGUAGE SQL
P2:
BEGIN
ATOMIC
DECLARE
v_importPathAndFileName
varchar
(100);
DECLARE
v_tabSchema
varchar
(100);
DECLARE
v_tabName
varchar
(100);
DECLARE
v_exportMSGPath
varchar
(100);
DECLARE
v_date
varchar
(12);
DECLARE
v_datetime
varchar
(30);
DECLARE
v_id
int
;
DECLARE
v_sql_import
varchar
(200);
DECLARE
v_sql_export
varchar
(200);
DECLARE
loc1 result_set_locator
varying
;
DECLARE
v_rows_read
VARCHAR
(100)
DEFAULT
''
;
DECLARE
v_rows_skipped
VARCHAR
(100)
DEFAULT
''
;
DECLARE
v_rows_inserted
VARCHAR
(100)
DEFAULT
''
;
DECLARE
v_rows_updated
VARCHAR
(100)
DEFAULT
''
;
DECLARE
v_rows_rejected
VARCHAR
(100)
DEFAULT
''
;
DECLARE
v_rows_committed
VARCHAR
(100)
DEFAULT
''
;
DECLARE
v_msg_retrieval
VARCHAR
(512)
DEFAULT
''
;
DECLARE
v_msg_removal
VARCHAR
(512)
DEFAULT
''
;
DECLARE
v_at_end
INT
DEFAULT
0;
DECLARE
SQLCODE
INT
DEFAULT
0;
DECLARE
SQLSTATE
CHAR
(5)
DEFAULT
'00000'
;
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
v_at_end = 1;
DECLARE
CONTINUE
HANDLER
FOR
SQLWARNING
begin
for
MSG_ID
as
SELECT
COALESCE
(
MAX
(msg_id),0)
as
id
from
importMSGOutput
do
set
v_id = MSG_ID.id + 1;
end
for
;
SET
v_datetime = to_char(
CURRENT
TIMESTAMP
,
'yyyy-mm-dd hh24:mi:ss'
);
INSERT
INTO
importMSGOutput
VALUES
(v_id, v_datetime,
' read rows:'
||v_rows_read ,
' skipped rows:'
||v_rows_skipped,
' inserted rows:'
||v_rows_inserted,
' updated rows:'
||v_rows_updated,
' rejected rows:'
||v_rows_rejected,
' committed rows:'
||v_rows_committed,
' msg_retrieval:'
||v_msg_retrieval,
' msg_removal:'
||v_msg_removal,
' importPathAndFileName:'
|| v_importPathAndFileName ||
', tabSchema:'
|| v_tabSchema ||
', tabName:'
|| v_tabName ||
', exportMSGPath:'
|| v_exportMSGPath,
' exception:SQLCODE-'
||SQLCODE ||
' SQLSTATE-'
||SQLSTATE);
end
;
DECLARE
UNDO HANDLER
FOR
SQLEXCEPTION
begin
for
MSG_ID
as
SELECT
COALESCE
(
MAX
(msg_id),0)
as
id
from
importMSGOutput
do
set
v_id = MSG_ID.id + 1;
end
for
;
SET
v_datetime = to_char(
CURRENT
TIMESTAMP
,
'yyyy-mm-dd hh24:mi:ss'
);
INSERT
INTO
importMSGOutput
VALUES
(v_id,v_datetime,
' read rows:'
||v_rows_read ,
' skipped rows:'
||v_rows_skipped,
' inserted rows:'
||v_rows_inserted,
' updated rows:'
||v_rows_updated,
' rejected rows:'
||v_rows_rejected,
' committed rows:'
||v_rows_committed,
' msg_retrieval:'
||v_msg_retrieval,
' msg_removal:'
||v_msg_removal,
' importPathAndFileName:'
|| v_importPathAndFileName ||
', tabSchema:'
|| v_tabSchema ||
', tabName:'
|| v_tabName ||
', exportMSGPath:'
|| v_exportMSGPath,
' exception:SQLCODE-'
||SQLCODE ||
' SQLSTATE-'
||SQLSTATE);
end
;
SET
v_importPathAndFileName =
COALESCE
(rtrim(ltrim(importPathAndFileName)),
''
);
SET
v_tabSchema =
COALESCE
(rtrim(ltrim(tabSchema)),
''
);
SET
v_tabName =
COALESCE
(rtrim(ltrim(tabName)),
''
);
SET
v_exportMSGPath =
COALESCE
(rtrim(ltrim(exportMSGPath)),
''
);
SET
v_date = to_char(
CURRENT
DATE
,
'yyyymmdd'
);
IF(v_importPathAndFileName !=
''
AND
v_tabSchema !=
''
AND
v_tabName !=
''
AND
v_exportMSGPath !=
''
)
THEN
SET
v_sql_import =
'import from '
|| v_importPathAndFileName ||
' of DEL modified by codepage=1375 DELPRIORITYCHAR messages on server insert into '
|| v_tabSchema ||
'.'
|| v_tabName;
CALL SYSPROC.admin_cmd(v_sql_import);
associate result
set
locator(loc1)
with
procedure
SYSPROC.admin_cmd;
allocate cursor1
cursor
for
result
set
loc1;
open
cursor1;
FETCH
cursor1
INTO
v_rows_read,v_rows_skipped,v_rows_inserted, v_rows_updated, v_rows_rejected,v_rows_committed,v_msg_retrieval,v_msg_removal;
WHILE v_at_end = 0
DO
for
MSG_ID
as
SELECT
COALESCE
(
MAX
(msg_id),0)
as
id
from
importMSGOutput
do
set
v_id = MSG_ID.id + 1;
end
for
;
SET
v_datetime = to_char(
CURRENT
TIMESTAMP
,
'yyyy-mm-dd hh24:mi:ss'
);
INSERT
INTO
importMSGOutput
VALUES
(v_id,v_datetime,
' read rows:'
||v_rows_read ,
' skipped rows:'
||v_rows_skipped,
' inserted rows:'
||v_rows_inserted,
' updated rows:'
||v_rows_updated,
' rejected rows:'
||v_rows_rejected,
' committed rows:'
||v_rows_committed,
' msg_retrieval:'
||v_msg_retrieval,
' msg_removal:'
||v_msg_removal,
' importPathAndFileName:'
|| v_importPathAndFileName ||
', tabSchema:'
|| v_tabSchema ||
', tabName:'
|| v_tabName ||
', exportMSGPath:'
|| v_exportMSGPath,
' '
);
END
WHILE;
SET
v_sql_export =
'export to '
|| v_exportMSGPath || v_tabSchema ||
'_'
|| v_tabName ||
'_'
|| v_date ||
'.load_out of DEL modified by STRIPLZEROS messages on server select * from importMSGOutput'
;
CALL SYSPROC.admin_cmd(v_sql_export);
close
cursor1;
begin
declare
cursor2
cursor
with
return
to
caller
for
select
*
from
importMSGOutput
where
msg_id = (
select
max
(msg_id)
from
importMSGOutput);
open
cursor2;
end
;
ELSEIF(v_exportMSGPath =
''
)
THEN
for
MSG_ID
as
SELECT
COALESCE
(
MAX
(msg_id),0)
as
id
from
importMSGOutput
do
set
v_id = MSG_ID.id + 1;
end
for
;
SET
v_datetime = to_char(
CURRENT
TIMESTAMP
,
'yyyy-mm-dd hh24:mi:ss'
);
INSERT
INTO
importMSGOutput
VALUES
(v_id,v_datetime,
' read rows:'
||v_rows_read ,
' skipped rows:'
||v_rows_skipped,
' inserted rows:'
||v_rows_inserted,
' updated rows:'
||v_rows_updated,
' rejected rows:'
||v_rows_rejected,
' committed rows:'
||v_rows_committed,
' msg_retrieval:'
||v_msg_retrieval,
' msg_removal:'
||v_msg_removal,
' importPathAndFileName:'
|| v_importPathAndFileName ||
', tabSchema:'
|| v_tabSchema ||
', tabName:'
|| v_tabName ||
', exportMSGPath:'
|| v_exportMSGPath,
' parameter - exportMSGPath can not null,otherwise can not export file.'
);
SET
v_sql_export =
'export to '
|| v_exportMSGPath || v_tabSchema ||
'_'
|| v_tabName ||
'_'
|| v_date ||
'.load_out of DEL modified by STRIPLZEROS messages on server select * from importMSGOutput'
;
CALL SYSPROC.admin_cmd(v_sql_export);
begin
declare
cursor2
cursor
with
return
to
caller
for
select
*
from
importMSGOutput
where
msg_id = (
select
max
(msg_id)
from
importMSGOutput);
open
cursor2;
end
;
ELSE
for
MSG_ID
as
SELECT
COALESCE
(
MAX
(msg_id),0)
as
id
from
importMSGOutput
do
set
v_id = MSG_ID.id + 1;
end
for
;
SET
v_datetime = to_char(
CURRENT
TIMESTAMP
,
'yyyy-mm-dd hh24:mi:ss'
);
INSERT
INTO
importMSGOutput
VALUES
(v_id,v_datetime,
' read rows:'
||v_rows_read ,
' skipped rows:'
||v_rows_skipped,
' inserted rows:'
||v_rows_inserted,
' updated rows:'
||v_rows_updated,
' rejected rows:'
||v_rows_rejected,
' committed rows:'
||v_rows_committed,
' msg_retrieval:'
||v_msg_retrieval,
' msg_removal:'
||v_msg_removal,
' importPathAndFileName:'
|| v_importPathAndFileName ||
', tabSchema:'
|| v_tabSchema ||
', tabName:'
|| v_tabName ||
', exportMSGPath:'
|| v_exportMSGPath,
' parameter - importPathAndFileName,tabSchema,tabName can not null'
);
SET
v_sql_export =
'export to '
|| v_exportMSGPath || v_tabSchema ||
'_'
|| v_tabName ||
'_'
|| v_date ||
'.load_out of DEL modified by STRIPLZEROS messages on server select * from importMSGOutput'
;
CALL SYSPROC.admin_cmd(v_sql_export);
begin
declare
cursor2
cursor
with
return
to
caller
for
select
*
from
importMSGOutput
where
msg_id = (
select
max
(msg_id)
from
importMSGOutput);
open
cursor2;
end
;
END
IF;
END
P2