07-13-2010
Extract data from DB2 tables and FTP it to outside company's firewall
Please help me in creating the script in AIX.
requirement is;
The new component's main function is to extract the data from DB2 tables and company's firewall directly.
The component function needs to check the timestamp in the DB2 tables ((CREDAT and CRETIM) with the requested timestamp and export the delta data to an .del (Delimiter) file format.
The date/timestamp columns in CIR tables -
Column Name Data Type & Length Null Description |
CREDAT CREDAT N IDOC create date |
CRETIM TIME N IDOC create timestamp |
|
|
1.2.1 Sample DB2 table and the data structures -
- WSOH (Assortment Modules)
- WSOP (Modules R/3 Retail and Line Item data)
Table -[/B] WSOH
Column Name |
Data Type & Length |
Null |
Description |
SYS_ID (UID) |
CHAR(3) |
Y |
System ID of SAP systems |
MANDT(UID) |
CHAR(3) |
Y |
SAP Client |
SKOPF(UID) |
CHAR(18) |
Y |
Assortment module |
DATAB |
DATE |
Y |
Validity from date |
DATUB |
DATE |
Y |
Validity end date |
STYPB |
CHAR(1) |
Y |
Module type |
LVORM |
CHAR(1) |
Y |
Deletion indicator IS-R tables |
ERSDA |
DATE |
N |
Creation date |
ERNAM |
CHAR(12) |
Y |
Name of person who created object |
PFLKN |
CHAR(1) |
Y |
Assortment priority |
STAT1 |
CHAR(1) |
Y |
Status (active/inactive) |
STAT2 |
CHAR(1) |
Y |
Status assignment promotion |
STAT3 |
CHAR(1) |
Y |
Status of assignment to assortments |
DATAE |
DATE |
Y |
The date last changed on |
AENAM |
CHAR(12) |
Y |
Name of accounting clerk who changed the record |
AKTIO |
CHAR(10) |
Y |
Promotion |
THEMA |
CHAR(4) |
Y |
Promotion theme |
FILIA |
CHAR(10) |
Y |
Assortment assignment(profile modules) |
WAGRP |
CHAR(9) |
Y |
Material group |
LOKAL |
CHAR(10) |
Y |
Local assortment for a retail plant |
TYPIN |
CHAR(1) |
Y |
Local module for the inclusion of (automat.) relistings |
WKLOK |
CHAR(4) |
Y |
Retail plant with a local assortment module |
WKWAG |
CHAR(4) |
Y |
Retail plant with profile module assignment |
LIFNR |
CHAR(10) |
Y |
Vendor number of a rack jobber |
RJPRO |
CHAR(1) |
Y |
Type of rack jobber processing |
LAYGR |
CHAR(10) |
Y |
Layout module |
LAYMOD_VER |
DECIMAL(5.0) |
Y |
Version of a layout module |
CREDAT |
DATE |
N |
IDOC create date |
CRETIM |
TIME |
N |
IDOC create timestamp |
|
Table - WSOP
Column Name |
Data Type & Length |
Null |
Description |
SYS_ID (UID) |
CHAR(3) |
Y |
System ID of SAP systems |
MANDT(UID) |
CHAR(3) |
Y |
SAP Client |
SKOPF(UID) |
CHAR(18) |
Y |
Assortment module |
SPOSI(UID) |
CHAR(18) |
Y |
Material number |
DATUB(UID) |
DATE |
Y |
|
Valid end date |
DATUV |
DATE |
Y |
Valid start date |
LVORM |
CHAR(1) |
Y |
Deletion indicator IS-R tables |
ERSDA |
DATE |
N |
Creation date |
ERNAM |
CHAR(12) |
Y |
Name of person who created object |
NEGAT |
CHAR(1) |
Y |
Negation of assortment item or materials |
PFLKN |
CHAR(1) |
Y |
Assortment priority |
URSAC |
CHAR(1) |
Y |
Listing cause (where does assortment item originate) |
SATNR |
CHAR(18) |
Y |
Cross-plant configuration material |
LSTFL |
CHAR(2) |
Y |
Listing procedure for shore or other assortment categories |
ATFSA |
CHAR(1) |
Y |
Selection ID “Generic material/structured material” |
KZFSA |
CHAR(1) |
Y |
ID : Variants of generic material chosen by selection |
SSTUF |
CHAR(2) |
Y |
Assortment grade |
LSTVZ |
CHAR(2) |
Y |
Listing procedure for store or other assortment categories |
CREDAT |
DATE |
N |
IDOC create date |
CRETIM |
TIME |
N |
IDOC create timestamp |
|
Table - WSOP
Column Name |
Data Type & Length |
Null |
Description |
SYS_ID (UID) |
CHAR(3) |
Y |
System ID of SAP systems |
MANDT(UID) |
CHAR(3) |
Y |
SAP Client |
SKOPF(UID) |
CHAR(18) |
Y |
Assortment module |
SPOSI(UID) |
CHAR(18) |
Y |
Material number |
DATUB(UID) |
DATE |
Y |
|
Valid end date |
DATUV |
DATE |
Y |
Valid start date |
LVORM |
CHAR(1) |
Y |
Deletion indicator IS-R tables |
ERSDA |
DATE |
N |
Creation date |
ERNAM |
CHAR(12) |
Y |
Name of person who created object |
NEGAT |
CHAR(1) |
Y |
Negation of assortment item or materials |
PFLKN |
CHAR(1) |
Y |
Assortment priority |
URSAC |
CHAR(1) |
Y |
Listing cause (where does assortment item originate) |
SATNR |
CHAR(18) |
Y |
Cross-plant configuration material |
LSTFL |
CHAR(2) |
Y |
Listing procedure for shore or other assortment categories |
ATFSA |
CHAR(1) |
Y |
Selection ID “Generic material/structured material” |
KZFSA |
CHAR(1) |
Y |
ID : Variants of generic material chosen by selection |
SSTUF |
CHAR(2) |
Y |
Assortment grade |
LSTVZ |
CHAR(2) |
Y |
Listing procedure for store or other assortment categories |
CREDAT |
DATE |
N |
IDOC create date |
CRETIM |
TIME |
N |
IDOC create timestamp |
1.2.2 FTPed
The output .del files need to be FTPed to outside company'sFirewall. The best FTP utility needs to be investigated.
1.2.3 AIX script
This component shall use an AIX script to include the db2 export process and FTP process. An completion log file or an error output log file should be generated
Last edited by priyanka3006; 07-13-2010 at 05:16 AM..
10 More Discussions You Might Find Interesting
1. Linux
Hi,
My Linux Mandrake 9.2 is dedicate to be a web server, ftpserver, dns, firewall and internet router.
With firewall enabled, I can't transfer files from a local computer to this machine.
I can still; however, connect to the ftp server but whenever I do ls, get, mget. my process gets frezze... (0 Replies)
Discussion started by: vtran4270
0 Replies
2. Shell Programming and Scripting
I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated.
Input:
test_data_1
1 2 90%
4 3 91%
5 4 90%
6 5 90%
9 6 90%
test_data_2
3 5 92%
5 4 92%
7 3 93%
9 2 92%
1 1 92%
...
Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies
3. UNIX for Dummies Questions & Answers
hi
i need to use unix to extract data from several rows of a table coded in html. I know that rows within a table have the tags <tr> </tr> and so i thought that my first step should be to to delete all of the other html code which is not contained within these tags. i could then use this method... (8 Replies)
Discussion started by: Streetrcr
8 Replies
4. Shell Programming and Scripting
My input file:
data_5 Ali 422 2.00E-45 102/253 140/253 24
data_3 Abu 202 60.00E-45 12/23 140/23 28
data_1 Ahmad 256 7.00E-45 120/235 140/235 22
data_4 Aman 365 8.00E-45 15/65 140/65 20
data_10 Jones 869 9.00E-45 65/253 140/253 18... (12 Replies)
Discussion started by: patrick87
12 Replies
5. AIX
The main function is to extract the data from DB2 tables and FTPed to outside the company's firewall directly.
pls find the attachment and help me out..i have only one day to do this (1 Reply)
Discussion started by: priyanka3006
1 Replies
6. Shell Programming and Scripting
HOw to extracts data from tables in database. Merges them into one output file. This output file is loaded into another tables in database. (1 Reply)
Discussion started by: nari.bommi
1 Replies
7. Shell Programming and Scripting
Hi,
I am creating a shell script to insert few records in db2 tables.
I am facing 2 challenges and would appreciate your help on this.
1) In my insert statement like follows:
db2 "connect to dbname user user_name";
db2 "insert into table_name (name, phone, ssn) values... (1 Reply)
Discussion started by: pinnacle
1 Replies
8. IP Networking
Hi!
My organization has put a Firewall which eat up a lot of important data access. So I came to know about SSH Tunneling to bypass the Firewall.
I will have to setup a free access SSH server to tunnel data access through PUTTY or OpenSSH.
The problem is that I don't know about any free... (1 Reply)
Discussion started by: nixhead
1 Replies
9. Shell Programming and Scripting
Hello All,
I have one table contains:
Table1:
5
10
30
40
60
80
...
Table 2:
10
20
60
80 (7 Replies)
Discussion started by: krsnadasa
7 Replies
10. UNIX for Beginners Questions & Answers
Hi I have a script which extracts the table from HTML and convert it into .csv.
But the problem in the script is if we have 2 tables in HTMl . it takes only the first table.
Please help me what changes i need to do in the script to make it read the complete HTML page.
Script is as below:
... (10 Replies)
Discussion started by: deepti01
10 Replies