Login or Register to Ask a Question and Join Our Community


AIX

Extract data from DB2 tables and FTP it to outside company's firewall

Login or Register to Reply

 
Thread Tools Search this Thread
Operating Systems AIX Extract data from DB2 tables and FTP it to outside company's firewall
# 1  
Old 07-13-2010
Data 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..
# 2  
Old 07-19-2010
How large is the file you are looking to transfer?

< 2GB, use SFTP or SCP
> 2GB, investigate commercial products
Padow
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Extract the tables from html

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

2. Shell Programming and Scripting

Getting input data from 2 tables

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

3. IP Networking

Bypassing My Company Firewall!

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

4. Shell Programming and Scripting

Help is Script inserting in db2 tables

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

5. Shell Programming and Scripting

Extracting data from tables......

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

6. AIX

extract data from DB2 and FTP it outside ompany's firewall

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

7. Shell Programming and Scripting

Extract data based on match against one column data from a long list data

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

8. UNIX for Dummies Questions & Answers

extract data from html tables

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

9. Shell Programming and Scripting

Converting tables of row data into columns of tables

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

10. Linux

Ftp to firewall

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
Login or Register to Ask a Question