SQL|Work:Useful SQL
SQL for WSG:
--User 1
--profile creation sql:
INSERT INTO TBL_AA_SUBJECT VALUES ( '1144567745518', 'User', 'Test6', 'Email 1', '01-Apr-2006', '', 'A', '', '04-Apr-2006', 'DEF-user-ecadmin', '', 'DEF-user-ecadmin', '0', '1')
--user group join sql:
INSERT INTO TBL_AA_SUBJECT2GROUP VALUES ( '1144567745518', 'REQ-group-ec', '04-Apr-2006', 'DEF-user-ecadmin', '', 'DEF-user-ecadmin', '1')
--user login sql
INSERT INTO TBL_AA_SUBJECT_LOGIN VALUES ( '1144567745518', 'Test6', '44rSFJQ9qtHWTBAvrsKd5K/p2j0=', '', '' ,'' ,'0' ,'' ,'DEF-user-ecadmin' ,'' ,'' ,'1' )
--==========================================
--check the latest SOS transaction from downstream
SELECT jo_message_id, jo_request_time, ip, jo_process, jo_service, jo_timediff, jo_returncode
FROM ec_journal WHERE jo_type = 'R'
AND jo_request_time >= '2007-05-12 14:40:00,000'
order by jo_message_id desc
AND jo_returncode = 'RC_SENT'
order by jo_message_id desc
and jo_process in ( 'DataEnrichProc', 'StaUpdProc')
and jo_process in ( 'NEProvProc')
--======================================
Transaction loading:
select ADATE, count(*) from (
select to_char( to_timestamp( jo_request_time, 'YYYY-MM-DD HH24:MI:SS,FF'), 'YYYY-MM-DD HH24') as ADATE
from ec_journal
where jo_type = 'R'
and jo_request_time > '2006-06-08 00:00:00,000'
and jo_request_time < '2006-06-08 23:59:00,000'
and jo_process not in ( 'DataEnrichProc', 'StaUpdProc')
)
group by ADATE;
select ADATE, count(*) from (
select to_char( to_timestamp( jo_request_time, 'YYYY-MM-DD HH24:MI:SS,FF'), 'YYYY-MM-DD HH24') as ADATE
from ec_journal
where jo_type = 'R'
and jo_request_time > '2006-06-08 00:00:00,000'
and jo_request_time < '2006-06-08 23:59:00,000'
)
group by ADATE;
select ADATE, count(*) from (
select to_char( to_timestamp( jo_request_time, 'YYYY-MM-DD HH24:MI:SS,FF'), 'YYYY-MM-DD HH24:MI') as ADATE
from ec_journal
where jo_type = 'R'
and jo_request_time > '2006-06-08 00:00:00,000'
and jo_request_time < '2006-06-08 23:59:59,000'
)
group by ADATE;
--==================================
--search journal records based on transactionid
SELECT a.* FROM ec_journal a , ec_keydata t0 WHERE
( a. jo_process is not NULL) and ( a. jo_process!= 'null')
AND a. jo_type = 'R' AND
a .jo_request_time >= '2006-06-07 09:02:34'
AND t0. attr = 'TransactionID' AND t0. value
LIKE '060607002445263%'
AND a .jo_message_id = t0. message_id
--search journal records based on contextid
SELECT a.* FROM ec_journal a , ec_keydata t0 WHERE
( a. jo_process is not NULL) and ( a. jo_process!= 'null')
AND a. jo_type = 'R' AND
a .jo_request_time >= '2006-06-07 09:02:34'
AND t0. attr = 'ContextID' AND t0. value = '931016'
AND a .jo_message_id = t0. message_id
select sum( jo_timediff), jo_message_id from ec_journal
where jo_message_id in
(
select transactionid from WSG_TXN_MASTER where STATUSFLAG= 'Y'
and transactionid > 060705000000000 and transactionid < 060721000000000
AND MESSAGENAME in (SELECT DISTINCT ( MESSAGENAME) FROM WSG_TXN_MASTER)
)
select jo_message_id, messagename, sum( jo_timediff) from (
select jo_message_id, messagename, jo_timediff from ec_journal a, wsg_txn_master b
where a. jo_message_id= b. transactionid
and b. transactionid > 060705000000000 and b .transactionid < 060721000000000
)
group by jo_message_id, messagename
SELECT MESSAGENAME, ( LASTUPDATEDDATE - CREATEDDATE) FROM WSG_TXN_MASTER
WHERE STATUSFLAG= 'Y'
AND MESSAGENAME in (
SELECT DISTINCT ( MESSAGENAME) FROM WSG_TXN_MASTER)
--=================================================
New Release:
--For Deactivate
update wsg_dealer_login set dealerstatus= 'N', loginstatus= 'N';
commit;
--test
update wsg_dealer_login set dealerstatus= 'Y', loginstatus= 'N'
where dealerid= 'RS120';
commit;
--For Reactivate
update wsg_dealer_login set dealerstatus= 'Y', loginstatus= 'N';
commit;
--============================================
SELECT ej. ip, ej. jo_service, ej. jo_timediff, ej. jo_request_time
FROM EC_JOURNAL ej, WSG_TXN_EVENTS ev
WHERE
ej. jo_type <> 'R' and
ej. JO_SERVICE LIKE '%FMLSdr%' and
ej. JO_MESSAGE_ID >= '070118000000000' and
ej. JO_MESSAGE_ID < '070119000000000' and
ev. transactionid= ej. JO_MESSAGE_ID and
ev. eventstatus = 'Infrastructure Error: TPETIME(13):0:0:TPED_MINVAL(0):QMNONE(0):0' and ej. jo_timediff > 60000
order by JO_MESSAGE_ID desc
=====================================================
SELECT * FROM WSG_TXN_EVENTS WHERE TRANSACTIONID >= '061116000000000'
and EVENTDESCRIPTION in( 'SP_Ack_Reply_Message', 'PortInInitiate_SP3_Reply' ) and SENDTOSP= 'Y'
order by 1
===================================================
Status check:
SELECT jo_message_id, jo_request_time, ip, jo_process, jo_service, jo_timediff, jo_returncode
FROM ec_journal WHERE jo_type = 'R'
AND jo_request_time >= '2007-02-15 17:50:13,000'
AND jo_returncode = 'RC_SENT'
select * from wsg_txn_master
order by createddate desc
select * from tbl_SPAck_Cache
============================================================
User audit:
select first_name, last_name, login_name, last_login_dt, i. group_name from
tbl_aa_subject a, tbl_aa_subject_login b, tbl_aa_subject2group h, tbl_aa_group i
where a. subject_id= b. SUBJECT_ID
and h. group_id= i. group_id
and a. subject_id= h. subject_id
============================================================
inactivate transaction:
update wsg_txn_master set statusflag= '' where transactionid= '061103000000594';
commit;
=============================================================
SELECT jo_message_id, jo_request_time, ip, jo_process, jo_service, jo_timediff, jo_returncode
FROM ec_journal WHERE jo_type = 'R' and jo_message_id in (
SELECT TRANSACTIONID FROM WSG_TXN_EVENTS WHERE TRANSACTIONID >= '061023000000000'
and errorcode is not NULL
--and eventstatus ='Infrastructure Error: TPETIME(13):0:0:TPED_MINVAL(0):QMNONE(0):0'
and eventstatus = 'java.lang.reflect.InvocationTargetException')
===================================================
Delay Ack check, the value is the main business transactionid:
select * from ec_keydata where value in
(
select message_id from ec_keydata where value= '061027006103437'
)
Delay Ack Transaction details:
SELECT jo_message_id, jo_request_time, ip, jo_process, jo_service, jo_timediff, jo_returncode
FROM ec_journal WHERE jo_type = 'R' and jo_message_id in (
select message_id from ec_keydata where value in
(
select message_id from ec_keydata where value= '061027006103437'
)
)
===================================================
New Release:
--For Deactivate
update wsg_dealer_login set dealerstatus= 'N', loginstatus= 'N';
commit;
--test
update wsg_dealer_login set dealerstatus= 'Y', loginstatus= 'N'
where dealerid= 'RS120';
commit;
--For Reactivate
update wsg_dealer_login set dealerstatus= 'Y', loginstatus= 'N';
commit;
======================================================================
============================================================================
Check which service takes long time:
FROM ec_journal WHERE jo_type <> 'R'
AND jo_request_time >= '2006-10-04 09:37:00,000'
order by jo_timediff desc
============================================================================
update wsg_err_master
set errordescription= 'The number of maximum transactions within last 10 minutes has been exceeded, please retry later'
where errorcode= '800404';
commit;
============================================================================
---Current
SELECT COUNT(*) FROM WSG_TXN_MASTER
WHERE DEALERID= 'RS225'
AND STATUSFLAG = ''
AND MESSAGENAME IN(
SELECT MESSAGENAME FROM WSG_MSG_MASTER
WHERE FLOODCONTROL= 'Y'
)
Please note a bug in the current SQL. To see which transactions are open it should be AND STATUSFLAG IS NULL
Putting that aside all we need to do is change SQL to this to find out how many in the last 10 minutes.
--Proposed
SELECT * FROM WSG_TXN_MASTER
WHERE DEALERID= 'RS225'
AND CREATEDDATE >= SYSDATE - ( 10/( 24* 60))
AND MESSAGENAME IN(
SELECT MESSAGENAME FROM WSG_MSG_MASTER
WHERE FLOODCONTROL= 'Y')
============================================================================
Useful SQL to find ACK?? s:
--- To find Ack message from MNP_Action_F1_Request
SELECT jo_message_id, jo_request_time, value
FROM ec_journal, ec_keydata WHERE jo_type = 'R'
AND jo_message_id in (
SELECT message_id FROM EC_KEYDATA
WHERE message_id>= '060926000000000'
AND ATTR= 'MSN'
AND VALUE= 'MNPAction_F1_Request' )
and jo_message_id= message_id
and ATTR= 'DealerID'
order by 1 desc
1 .
--------------------------------------------------------------------------------
From: Stephen Cheng
Sent : Friday, 28 July 2006 11 :05 AM
To: Bradley Miller
Subject : FW: Production stats
I just come out of the average time for per type of transaction, but I think that value is
meaningless for some transactions.
Business transaction response time:
Basic :
select messagename, avg( aday) from (
SELECT MESSAGENAME, ( to_date( to_char( LASTUPDATEDDATE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
- to_date( to_char( CREATEDDATE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
) as aday FROM WSG_TXN_MASTER
WHERE STATUSFLAG= 'Y'
AND MESSAGENAME in (
SELECT DISTINCT ( MESSAGENAME) FROM WSG_TXN_MASTER)
)
group by messagename
enhanced : limit data range and message name for fast query in production.
select messagename, avg( aday)* 24* 3600 as seconds from (
SELECT MESSAGENAME, ( to_date( to_char( LASTUPDATEDDATE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
- to_date( to_char( CREATEDDATE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
) as aday FROM WSG_TXN_MASTER
WHERE STATUSFLAG= 'Y'
and transactionid> 060711000000000 and transactionid< 060727999999999
AND MESSAGENAME in (
'ActivateServiceRequest' ,
'FileTransferRequest' ,
'LogIn_SP3_Request' ,
'ModifySIMRequest' ,
'ModifyServiceRequest' ,
'PortInInitiateRequest' ,
'PortInServiceOrderRequest' )
)
group by messagename
Not slow for Aug:
select messagename, avg( aday)* 24* 3600 as seconds from (
SELECT MESSAGENAME, ( to_date( to_char( LASTUPDATEDDATE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
- to_date( to_char( CREATEDDATE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
) as aday FROM WSG_TXN_MASTER
WHERE STATUSFLAG= 'Y'
and transactionid> 060801000000000 and transactionid< 060831999999999
AND MESSAGENAME in (
SELECT DISTINCT ( MESSAGENAME) FROM WSG_TXN_MASTER)
)
group by messagename
Thanks
Stephen Cheng
|Email : Stephen. Cheng@ optus. com. au
| Work: 02 90271620 Fax: 02 90271811
|Mobile :0413361769
===============================================================================
Transaction response time through ec_journal time_diff:
--------------------------------------------------------------------------------
2 .
Grouped per minute
select ADATE, count(*) from (
select to_char( to_timestamp( jo_request_time, 'YYYY-MM-DD HH24:MI:SS,FF'), 'YYYY-MM-DD HH24:MI') as ADATE
from ec_journal
where jo_type = 'R'
and jo_request_time > '2006-09-08 10:12:00,000'
and jo_request_time < '2006-09-08 10:13:00,000'
)
group by ADATE;
Business Transaction Grouped Per Hour
select ADATE, count(*) from (
select to_char( to_timestamp( jo_request_time, 'YYYY-MM-DD HH24:MI:SS,FF'), 'YYYY-MM-DD HH24') as ADATE
from ec_journal
where jo_type = 'R'
and jo_request_time > '2006-05-02 00:00:00,000'
and jo_request_time < '2006-05-03 23:59:00,000'
and jo_process not in ( 'DataEnrichProc', 'StaUpdProc')
)
group by ADATE;
Overall Loading Grouped Per Hour
select ADATE, count(*) from (
select to_char( to_timestamp( jo_request_time, 'YYYY-MM-DD HH24:MI:SS,FF'), 'YYYY-MM-DD HH24') as ADATE
from ec_journal
where jo_type = 'R'
and jo_request_time > '2006-05-02 00:00:00,000'
and jo_request_time < '2006-05-03 23:59:00,000'
)
group by ADATE;
--------------------------------------------------------------------------------
3 .
A .
running me a report to show number of transaction we have process
for each of transactions type mention below from 1 July 2006 to 31 July 2006 for all messages
sample report loading categorized by process:
select jo_process, count( jo_process) from ec_journal
where jo_type= 'R'
AND jo_request_time >= '2006-08-25 10:30:00,000'
AND jo_request_time <= '2006-08-28 10:30:00,000'
group by jo_process
result :
COUNT (JO_PROCESS ) JO_PROCESS
2 DataEnrichProc
13 MNPFmlProc
10 SPDelayAckProc
14 SktProc
12 StaUpdProc
====
B .
Sample:query the count number in a certain period
select messagename, count( messagename) from wsg_txn_master
where transactionid> 060825000000000
AND transactionid< 060830000000000
group by messagename
Result :
MESSAGENAME COUNT( MESSAGENAME)
LogIn_SP3_Request 6
MNPNotification_F1_Notify 11
PortInInitiate_SP3_Request 2
PortInServiceOrder_SP3_Request 2
For july's report
select jo_process, count( jo_process) from ec_journal
where jo_type= 'R'
AND jo_request_time >= '2006-07-01 00:00:00,000'
AND jo_request_time <= '2006-07-31 23:59:59,000'
group by jo_process
select messagename, count( messagename) from wsg_txn_master
where transactionid> 060701000000000
AND transactionid< 060731999999999
group by messagename
============================================================================================================
--unblock a user account
update tbl_aa_subject_login set attempts_made= 0 where login_name= 'mkhan';
commit;
update tbl_aa_subject set status= 'A' where subject_id in (
select subject_id from tbl_aa_subject_login where login_name= 'mkhan'
);
commit;
=================================================================================================================
search business transactions by mobile phone number
select a. value, a. attr, b. transactionid, b. createddate, b. statusflag, b. messagename
from ec_keydata a, wsg_txn_master b
where a. message_id = b. transactionid
and attr in ( 'MSN', 'MSNSO' )
and b. billingoption = 'prepaid'
and b. dealerid= 'RS161'
--and b.transactionid>='060830000000000'
and a. VALUE= '0434450011'
order by 1
Sample:
VALUE ATTR TRANSACTIONID CREATEDDATE STATUSFLAG MESSAGENAME
0434450011 MSNSO 060831004579278 31 /08 /2006 11 :33 :53.125000 AM N ActivateServiceRequest
================================================
check a dealer's status
select * from wsg_txn_master
where dealerid= 'RS161' and
transactionid >061013000000000
order by transactionid desc
=================================================
Activate transaction in production
update wsg_txn_master set statusflag= '' where transactionid= '061018005888798';
commit;
===================================================
table size:
select segment_name, bytes from user_segments where segment_name= 'WSG_TXN_MASTER'
avg row len:
select table_name, AVG_ROW_LEN from user_tables
where table_name in ( 'WSG_TXN_MASTER', 'WSG_TXN_EVENTS', 'EC_JOURNAL', 'EC_KEYDATA', 'EC_MESSAGES', 'EC_TEMP_MESSAGES')
====================================================================================
There are two useful statement for you to check the traffic for a specific dealer:
" This will check the MSN and relevant business transaction times in a certain period.
In this statement, it's very important to limit the query period by a constraint: TRANSACTIONID>='060915000000000 ', only limit it to 1 or 2 days.
SELECT DISTINCT ( VALUE), COUNT(*) FROM EC_KEYDATA WHERE MESSAGE_ID IN(
SELECT TRANSACTIONID FROM WSG_TXN_MASTER WHERE TRANSACTIONID>= '060915000000000'
AND DEALERID= 'RS225' and MESSAGEPROTOCOL= 'XML')
AND ATTR= 'MSN' GROUP BY VALUE
VALUE COUNT(*)
041556655 1
041589654 1
0419002099 3
0419002930 4
0432700010 411
0432700165 4
0432700825 91
0432700847 14
0432700850 4
0432700853 11
0432700874 4
0432700942 4
0432701221 29337
0432701369 5
0432701607 58735
0432701921 17
0432701922 23
0432701923 121
0488110118 2
0488110414 4
0488110416 4
0488110420 6
0488110456 16
"
This is just to find out what number is being used but maybe a count from WSG_TXN_MASTER is also useful
SELECT * FROM WSG_TXN_MASTER WHERE TRANSACTIONID>= '060915000000000'
AND DEALERID= 'RS225' and MESSAGEPROTOCOL= 'XML'
order by transactionid asc
--======================================================================================================
SQL for Proton:
select jo_process, jo_service, jo_message_id, jo_request_time from ec_journal
where jo_service like '%VMSktSRLSdrSrv%'
and jo_message_id > '061213010372550'
and ip= '10.8.38.34'
--=============================================
--end to end File in and out
-- Insert FILE transfer IN test records
INSERT INTO GW_ACCT_ACCESS ( ACCT_ACCESS_ID, ACCESS_TYPE, ACCT_ID) VALUES ( 103 , 'F', 1 );
INSERT INTO GW_FILE_ACCOUNT ( ACCT_ACCESS_ID, LOCAL_LCTN_RECV, LOCAL_LCTN_SEND)
VALUES ( 103, '/apps/dncg/sit/filestore/exchange/IDW/RECV/' , '/apps/dncg/sit/filestore/exchange/IDW/SEND/' );
INSERT INTO GW_TRSFR_RULE ( RULE_ID, DIRECTION, NTFCTN_LVL_CODE, NTFCTN_ADDR, TRX_STATE_CODE,
KEEP_COPY_FLG , COMPRESS_FLG, MINS_BTW_RUN, DT_NEXT_RUN, ACCESS_ACCT_ID, COMP_NAME_PATTERN)
VALUES ( 103 , 'I', 'E' , 'peter.wrzos@optus.com.au', 'I', 'Y' , 'N', 2 ,
TO_DATE ( '12/30/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 103 ,
'.*_[^_]+_CAMP_[0-9]{6}_[0-9]{6}_[0-9]{8}_W.dat' );
INSERT INTO GW_TRSFR_RECV ( RULE_ID, ForWarD_flg) VALUES ( 103, 'D' );
-- Insert FILE transfer OUT test records
INSERT INTO GW_ACCT_ACCESS ( ACCT_ACCESS_ID, ACCESS_TYPE, ACCT_ID) VALUES ( 104 , 'F', 1 );
INSERT INTO GW_FILE_ACCOUNT ( ACCT_ACCESS_ID, LOCAL_LCTN_RECV, LOCAL_LCTN_SEND) VALUES
( 104, '/apps/dncg/sit/filestore/exchange/IDW/RECV/' , '/apps/dncg/sit/filestore/exchange/IDW/SEND/' );
INSERT INTO GW_TRSFR_RULE ( RULE_ID, DIRECTION, NTFCTN_LVL_CODE, NTFCTN_ADDR,
TRX_STATE_CODE , KEEP_COPY_FLG, COMPRESS_FLG, MINS_BTW_RUN, DT_NEXT_RUN, ACCESS_ACCT_ID,
COMP_NAME_PATTERN ) VALUES ( 104, 'O' , 'E', 'peter.wrzos@optus.com.au' , 'P', 'Y', 'N' , 2,
TO_DATE ( '12/30/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 104 ,
'.*_[^_]+_CAMP_[0-9]{6}_[0-9]{6}_[0-9]{8}_W.dat');
INSERT INTO GW_TRSFR_RECV ( RULE_ID, ForWarD_flg) VALUES ( 104, 'D' );
commit;
SELECT T. RULE_ID, A. ACCT_ID, T. NTFCTN_LVL_CODE, T. NTFCTN_ADDR, AA. ACCESS_TYPE, T. COMP_NAME_PATTERN MATCHING_PATTERN, A. IC_CODE INTERFACE_CONTRACT, S. HOST_NAME SFTP_HOST_NAME, S. USER_NAME SFTP_USER_NAME, S.PASSWORD SFTP_PASSWORD, S. REMOTE_LCTN_SEND SFTP_LOCATION_FROM, S. REMOTE_LCTN_RECV SFTP_LOCATION_TO, E. HOST_NAME EMAIL_HOST_NAME, E. MAILBOX EMAIL_MAILBOX, E.PASSWORD EMAIL_PASSWORD, F. LOCAL_LCTN_SEND LOCAL_LOCATION_TO, F. LOCAL_LCTN_RECV LOCAL_LOCATION_FROM, A. NTRNL_LCTN_RECV TO_DIR, A. NTRNL_LCTN_SEND FROM_DIR, T. DT_NEXT_RUN, T. MINS_BTW_RUN, T. DIRECTION, T. TRX_STATE_CODE TRX_STATE, R. FORWARD_FLG FORWARD_TYPE FROM GW_TRSFR_RULE T, GW_ACCOUNT A, GW_TRSFR_RECV R, GW_ACCT_ACCESS AA LEFT JOIN GW_SFTP_ACCOUNT S ON AA. ACCT_ACCESS_ID = S. ACCT_ACCESS_ID LEFT JOIN GW_EMAIL_ACCOUNT E ON AA. ACCT_ACCESS_ID = E. ACCT_ACCESS_ID LEFT JOIN GW_FILE_ACCOUNT F ON AA. ACCT_ACCESS_ID = F. ACCT_ACCESS_ID WHERE T. ACCESS_ACCT_ID = AA. ACCT_ACCESS_ID AND AA. ACCT_ID = A. ACCT_ID AND T. RULE_ID = R. RULE_ID
update gw_trsfr_rule set mins_btw_run= 1000 where rule_id= 3;
commit;
----------------------------------------------------------------------------------------------
--end to end File in and out
-- Insert FILE transfer IN test records
INSERT INTO GW_ACCT_ACCESS ( ACCT_ACCESS_ID, ACCESS_TYPE, ACCT_ID) VALUES ( 103 , 'F', 1 );
INSERT INTO GW_FILE_ACCOUNT ( ACCT_ACCESS_ID, LOCAL_LCTN_RECV, LOCAL_LCTN_SEND)
VALUES ( 103, '/apps/dncg/dev/filestore/exchange/IDW/RECV/' , '/apps/dncg/dev/filestore/exchange/IDW/SEND/' );
INSERT INTO GW_TRSFR_RULE ( RULE_ID, DIRECTION, NTFCTN_LVL_CODE, NTFCTN_ADDR, TRX_STATE_CODE,
KEEP_COPY_FLG , COMPRESS_FLG, MINS_BTW_RUN, DT_NEXT_RUN, ACCESS_ACCT_ID, COMP_NAME_PATTERN)
VALUES ( 103 , 'I', 'E' , 'Stephen.cheng@optus.com.au', 'I', 'Y' , 'N', 2 ,
TO_DATE ( '12/30/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 103 ,
'IDW_[^_]+_CAMP_[0-9]{6}_[0-9]{6}_[0-9]{8}_W.dat' );
INSERT INTO GW_TRSFR_RECV ( RULE_ID, ForWarD_flg) VALUES ( 103, 'D' );
-- Insert FILE transfer OUT test records
INSERT INTO GW_ACCT_ACCESS ( ACCT_ACCESS_ID, ACCESS_TYPE, ACCT_ID) VALUES ( 104 , 'F', 1 );
INSERT INTO GW_FILE_ACCOUNT ( ACCT_ACCESS_ID, LOCAL_LCTN_RECV, LOCAL_LCTN_SEND) VALUES
( 104, '/apps/dncg/dev/filestore/exchange/IDW/RECV/' , '/apps/dncg/dev/filestore/exchange/IDW/SEND/' );
INSERT INTO GW_TRSFR_RULE ( RULE_ID, DIRECTION, NTFCTN_LVL_CODE, NTFCTN_ADDR,
TRX_STATE_CODE , KEEP_COPY_FLG, COMPRESS_FLG, MINS_BTW_RUN, DT_NEXT_RUN, ACCESS_ACCT_ID,
COMP_NAME_PATTERN ) VALUES ( 104, 'O' , 'E', 'Stephen.cheng@optus.com.au' , 'P', 'Y', 'N' , 2,
TO_DATE ( '12/30/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 104 ,
'IDW_[^_]+_CAMP_[0-9]{6}_[0-9]{6}_[0-9]{8}_W.dat');
INSERT INTO GW_TRSFR_RECV ( RULE_ID, ForWarD_flg) VALUES ( 104, 'D' );
commit;
SELECT T. RULE_ID, A. ACCT_ID, T. NTFCTN_LVL_CODE, T. NTFCTN_ADDR, AA. ACCESS_TYPE, T. COMP_NAME_PATTERN MATCHING_PATTERN, A. IC_CODE INTERFACE_CONTRACT, S. HOST_NAME SFTP_HOST_NAME, S. USER_NAME SFTP_USER_NAME, S.PASSWORD SFTP_PASSWORD, S. REMOTE_LCTN_SEND SFTP_LOCATION_FROM, S. REMOTE_LCTN_RECV SFTP_LOCATION_TO, E. HOST_NAME EMAIL_HOST_NAME, E. MAILBOX EMAIL_MAILBOX, E.PASSWORD EMAIL_PASSWORD, F. LOCAL_LCTN_SEND LOCAL_LOCATION_TO, F. LOCAL_LCTN_RECV LOCAL_LOCATION_FROM, A. NTRNL_LCTN_RECV TO_DIR, A. NTRNL_LCTN_SEND FROM_DIR, T. DT_NEXT_RUN, T. MINS_BTW_RUN, T. DIRECTION, T. TRX_STATE_CODE TRX_STATE, R. FORWARD_FLG FORWARD_TYPE FROM GW_TRSFR_RULE T, GW_ACCOUNT A, GW_TRSFR_RECV R, GW_ACCT_ACCESS AA LEFT JOIN GW_SFTP_ACCOUNT S ON AA. ACCT_ACCESS_ID = S. ACCT_ACCESS_ID LEFT JOIN GW_EMAIL_ACCOUNT E ON AA. ACCT_ACCESS_ID = E. ACCT_ACCESS_ID LEFT JOIN GW_FILE_ACCOUNT F ON AA. ACCT_ACCESS_ID = F. ACCT_ACCESS_ID WHERE T. ACCESS_ACCT_ID = AA. ACCT_ACCESS_ID AND AA. ACCT_ID = A. ACCT_ID AND T. RULE_ID = R. RULE_ID AND SYSDATE>= DT_NEXT_RUN
SELECT T. RULE_ID, A. ACCT_ID, T. NTFCTN_LVL_CODE, T. NTFCTN_ADDR, AA. ACCESS_TYPE, T. COMP_NAME_PATTERN MATCHING_PATTERN, A. IC_CODE INTERFACE_CONTRACT, S. HOST_NAME SFTP_HOST_NAME, S. USER_NAME SFTP_USER_NAME, S.PASSWORD SFTP_PASSWORD, S. REMOTE_LCTN_SEND SFTP_LOCATION_FROM, S. REMOTE_LCTN_RECV SFTP_LOCATION_TO, E. HOST_NAME EMAIL_HOST_NAME, E. MAILBOX EMAIL_MAILBOX, E.PASSWORD EMAIL_PASSWORD, F. LOCAL_LCTN_SEND LOCAL_LOCATION_TO, F. LOCAL_LCTN_RECV LOCAL_LOCATION_FROM, A. NTRNL_LCTN_RECV TO_DIR, A. NTRNL_LCTN_SEND FROM_DIR, T. DT_NEXT_RUN, T. MINS_BTW_RUN, T. DIRECTION, T. TRX_STATE_CODE TRX_STATE, R. FORWARD_FLG FORWARD_TYPE FROM GW_TRSFR_RULE T, GW_ACCOUNT A, GW_TRSFR_RECV R, GW_ACCT_ACCESS AA LEFT JOIN GW_SFTP_ACCOUNT S ON AA. ACCT_ACCESS_ID = S. ACCT_ACCESS_ID LEFT JOIN GW_EMAIL_ACCOUNT E ON AA. ACCT_ACCESS_ID = E. ACCT_ACCESS_ID LEFT JOIN GW_FILE_ACCOUNT F ON AA. ACCT_ACCESS_ID = F. ACCT_ACCESS_ID WHERE T. ACCESS_ACCT_ID = AA. ACCT_ACCESS_ID AND AA. ACCT_ID = A. ACCT_ID AND T. RULE_ID = R. RULE_ID
update gw_trsfr_rule set mins_btw_run= 1000 where rule_id= 1;
commit;
select * from GW_TRSFR_RULE
update GW_TRSFR_RULE set comp_name_pattern= '.*_[^_]+_CAMP_[0-9]{6}_[0-9]{6}_[0-9]{8}_W.dat'
where access_acct_id= 104
SELECT VAL, DESCR
FROM GW_VALUE V, GW_VALUE_SET S, GW_IC_ATTRIBUTE A
WHERE V. VAL_ID = S. VAL_ID
AND S. SET_ID = A. ALL_VAL_SET_ID
AND A. NAME = 'COMPRESSION' ;
select * from gw_trsfr_rule
select * from dncg_trx_id_seq
delete from dncg_trx_id_seq;
commit;
update dncg_trx_id_seq set seq_num= 1 , run_num= 0 where party_id= 'SFTP' and content_type_code= 'CAMP'
select * from gw_trsfr
select * from gw_file
select * from gw_transaction
INSERT INTO GW_FILE ( FILE_ID, FILE_LCTN, FILE_NAME, COMPRESS_FLG, NUM_RECORDS) VALUES ( 59, '/apps/dncg/dev/filestore/internal/IDW/SEND/' , 'DNCG_SFTP_CAMP_000000_000001_00000001_W.DAT' , ' ', 1 )
INSERT INTO GW_TRSFR ( TRSFR_ID, FILE_ID, TRX_ID, STATUS, DT_CREATED, OUTDATED_FLG, RULE_ID)
VALUES ( 60, 59 , 58, 'P' , SYSDATE, ' ', 4 )
SELECT T. TRSFR_ID, F. FILE_NAME, F. FILE_LCTN FROM GW_TRSFR T, GW_FILE F
WHERE T. FILE_ID = F. FILE_ID AND T. STATUS = 'P' AND T. RULE_ID = '104'
select * from gw_trsfr_rule
SELECT T. TRX_ID FROM GW_TRANSACTION T
JOIN GW_TRX_KEYDATA K1 ON T. TRX_ID= K1. TRX_ID
JOIN GW_TRX_KEYDATA K2 ON T. TRX_ID= K2. TRX_ID
JOIN GW_TRX_KEYDATA K3 ON T. TRX_ID= K3. TRX_ID
AND K1. ATTR_ID= 2 AND K1. VAL= 'SMB'
AND K2. ATTR_ID= 3 AND K2. VAL= 'CAMP'
AND K3. ATTR_ID= 4 AND K3. VAL= '000000'
select * from GW_TRANSACTION
select * from GW_TRX_KEYDATA
delete from dncg_trx_id_seq;
commit;
update dncg_trx_id_seq set seq_num= 1, run_num= 1;
commit;
delete from GW_TRX_KEYDATA ;
commit;
select * from dncg_trx_id_seq
SELECT T. TRSFR_ID, T. TRX_ID, F. FILE_NAME, F. FILE_LCTN FROM GW_TRSFR T, GW_FILE F
WHERE T. FILE_ID = F. FILE_ID AND T. STATUS = 'P' AND T. RULE_ID = '104'
select * from GW_TRSFR where rule_id= '104'
select * from GW_FILE
-----------------------------------------------------------------------------------------------
No comments:
Post a Comment