Menu item 40: Export DB/2 file(s) (Part 1)
Introduction
*DBEXPORT helps IBM Power Systems locations delivering high-quality information to their customers and users more quickly and more simply than it has been possible previously.
i-effect® *DBEXPORT provides tools for exporting Power Systems database tables as PC format stream files.
The Power Systems database (UDB for System i) is an advanced, multifunctional, easily managed, relational database, in which large quantities of data can be stored securely. The data stored there can be retrieved and updated quickly and efficiently. Many users want to access and manipulate data in the database using tools such as spread-sheets, PC databases and business applications. Enabling users to access data in the database in the required form is a challenge many Power Systems companies are facing every day.
One frequently used approach to this problem is to allow users to import data from the Power Systems database into PC applications using client access file transfers or ODBC queries.
These solutions have a number of drawbacks, mainly because they are "pull" tech-nologies. In other words, client access file transfer or ODBC query have to be initiated from the PC end and driving them by the Power Systems is not possible. This has certain disadvantageous consequences.
The process must be run by users at the keyboard. This typically means that these operations must be run during office hours, which imposes an unnecessary load on the Power Systems and the network during the busiest hours of the day. It also means that time is wasted with data preparation. Wouldn't it be better if the data was waiting for them when they arrive in the office?
Users have to run the process at the right time. What happens if the job creating or updating the desired data has not finished running yet? Do they get yesterday's data instead? Could data get lost, corrupted or duplicated? Wouldn't it be better to integrate and schedule the preparation of PC data with batch processes in order that the PC data is not created until Power Systems is ready?
Users have to make sure to push the right buttons. Running regular file transfer and queries can be a repetitive, tedious, and error-prone task. Human beings are fallible and mistakes will occur from time to time. Wouldn't it be better if these processes were automated?
The *DBEXPORT module, in contrast, is a "push" technology. It provides a set of integrated tools to entirely extract PC format data from the Power Systems database, without the need for PC or user intervention. This means that the entire process can be fully automated and controlled by programs; it can be easily integrated with existing applications and batch schedules.
The *DBEXPORT module can process any of the following as input sources:
- SQL queries from the command promt
- SQL query statements stored in a source file member
- Query/400 queries
- Query Management (QM/400) queries
- Physical Power Systems (AS/400) Files
- Logical Power Systems (AS/400) files
The *DBEXPORT module can output the results or contents of these input sources as stream files in any of the following file formats:
- Excel (either BIFF 8 format compatible with Excel 97 or higher) or 5 format (compatible with Excel 5 or higher).
- HTML
- Comma Separated Variable (CSV) and other delimited text formats
- Fixed Text Format
This means that a piece of SQL can be executed and its output saved directly as an Excel spreadsheet, simply by running one command on the IBM System i!
Similarly, a query can be run and its output saved as an ASCII CSV and then opened with Lotus 123. The latest sales figures can be displayed on the Intranet, rather than printing a paper report as before, by saving the output from the sales reporting program as a temporary database file, and using *DBEXPORT to convert it to HTML.
Stream files created by *DBEXPORT can be saved anywhere in the Systems i's IFS (Integrated File System). Thus, they can be held on the Power Systems itself (using the root or QDLS file systems), or transferred seamlessly to a Windows server (using the QNTC file system) or to another Power Systems (using the QFileSvr.400 file system).
*DBEXPORT provides a number of additional functions, when processing physical or logical Power Systems files, which make delivering high-quality, easily accessible data to users easier:
Record Sorting | Records can be sorted by multiple key fields in ascending or descending sequence. |
Record Selection | Records can be selected or omitted, based on criteria that you define in a familiar format |
Include/ Omit Fields | The order in which the fields appear can be specified by listing them in that order. |
Field Sorting | The order in which the fields appear can be specified by listing them in that order. |
Formatting | Date, time, and number formats can be managed and edit codes and edit words are reflected in Excel formatting. |
Application
*DBEXPORT centers on the CVTDBF (Convert Database File to Stream File) command, which can take any Power Systems database file (i.e. DB2 UDB for Power Systems file or table) and convert it to formats that can be used by popular PC applications such as spreadsheets, databases, and browsers.
*DBEXPORT can deliver data to users more efficiently and reliably then before.
Automated Distribution of Reports and Documents
Still sending paper reports to employees? Still sending data to customers in the mail?
Wouldn't it be more efficient to send the same information to users in electronic form, which can be opened directly with PC spreadsheets without them having to re-key it? *DBEXPORT can modify applications to deliver data to users in the form they require. All that is required, is a change to programs so that they output to a database file rather than a spooled file. *DBEXPORT can be used to create an Excel spread-sheet, CSV, HTML, or fixed text file from the database file.
The files which were created with *DBEXPORT can be sent via email to anyone, instead of sending them on paper. This allows all users to handle the files. It also simplifies business processes and saves money.
Integration with normal Batch Jobs
If a finance department needed to analyze sales figures from the previous week on Monday morning, they may have to move the information from the sales database to Excel. Users could perform a Client Access file transfer or run an ODBC query. This may seem like the easiest way. However, *DBEXPORT offers some significant advantages over these methods:
- Data is ready for users as soon as they require it. Conversions can be run overnight. Users simply connect to the AS/400 and open a file from the IFS or a PC server in the morning. Time is not wasted running file transfers or ODBC queries.
- Data can be prepared when convenient. Conversions can be easily integrated with normal overnight or weekend batch jobs, and therefore run after hours when the load on the system and network is lowest. There's no need for a PC at all and no need for complicated, error-prone AS/400-to-PC communications to trigger file transfers or ODBC queries .
- Conversion does not occur until data is ready. Run the conversion step immediate-ly after the step that creates the data to ensure that the two occur in the right sequence.
- Since the conversion parameters are built into applications, conversion will be done correctly every time, automatically. Users need not run the transfer or the query correctly each day or each week, or remember which buttons to press and which selections to make each time .
For example, rather than having users run ODBC queries against the sales database on a Monday morning, the job can be run over the weekend to extract information about sales made the previous week. This could be as simple as running a Query/400 query. With *DBEXPORT the query can be run and saved as an Excel, CSV file, HTML, or ASCII text file in the IFS. Users only need a network drive assigned to the right Power Systems and the necessary permission to access the data. They can simply load their spreadsheet application Monday morning, open the file previously created using CVTDBF, and work with it directly.
The advantage to this approach is that the Power Systems controls, schedules and manages creation of PC format data entirely. It saves time and effort. It prevents user errors with file transfer, and runs ODBC queries at the right time, and lightens the System i's workload during office hours.
Data Sharing
Often, many users will need the same data. This can sometimes mean that different users will be running the same or very similar queries or file transfers against the database the same day. This is very inefficient. Why not run that query for them once, overnight, and save the results as an Excel, CSV, or HTML file to a central server so the information can be shared by everyone?
These files can also be published on a website for customers worldwide to see, or on secure intranet or extranet.
Stream files created by *DBEXPORT can be saved anywhere in the System i's IFS (Integrated File System).
Formats
CVTDBF takes a Power Systems database file (physical or logical) and converts it to a PC file stored as a stream file on the Power Systems. The stream file can be saved anywhere in the IFS (Integrated File System) and is available for use by any authorized PC user assigned to a suitable Client Access network drive.
Several different file formats can be chosen, depending on individual needs. Additional formatting options will be introduced in future versions of the product. At the moment, the supported formats are:
Excel-Format | If TOFMT(*XLS) is specified, CVTDBF will create an Excel (TM) spreadsheet from the database file. The default is to create a BIFF 8 file (compatible with Excel 97, Excel 2000, Excel XP, and above). A BIFF 5 file can also be chosen (compatible with Excel 5.0 and above) if the spreadsheet does not support the Excel file format. MS Works files created by CVTDBF in Excel format can be opened directly by any application supporting the Excel file format, such as Microsoft Excel but also Lotus 123 and MS Works spreadsheet. |
Delimited ASCII Text (CSV) | CVTDBF can also create delimited ASCII text files, for example a CSV (comma-separated variable file). This format is ideal for loading reports containing columns of numbers into a spreadsheet, Business Intelligence tool, or other application for further manipulation. CVTDBF can use any field delimiter desired (by default a comma, but also tabs, blanks etc.) and any string separator specified (by default a double quote „), and generates files in the precise PC format required. |
HTML (Hypertext Mark-up Language) | If data is to be viewed in a browser, such as Netscape® Navigator, Mozilla® Firefox, or Microsoft® Internet Explorer, CVTDBF can help. HTML is the language in which web pages are normally written. CVTDBF can convert database files to a formatted HTML table, which provides a neat way of displaying your data on the Internet, or on your company Intranet or Extranet . |
ASCII Text | CVTDBF can also simply convert a Power Systems database file to a simple ASCII text file with fixed-length data columns without delimiters or separators. This format can be readily processed by user written applications so long as the file layout is known in advance, and can be useful for exchanging data with business partners in EDI operations. |
Formatting | Date, time, and number formats as well as editing codes/words can be handled or reproduced. |
FROMFILE (From File) | The FROMFILE (From File) parameter specifies the name and library of the database file that contains the records to be converted. One of the special values explained below can be used to run a query and save the output to a stream file as a single operation. A database file can be a physical file or a logical file . | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
This parameter contains 3 elements: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
From File (FROMFILE) | The first element of the FROMFILE parameter is the qualified name of the file that contains the data to be converted or one of the special values listed below. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Unless one of the special values listed below is entered, the file name must be specified as:
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
The possible library values are:
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Possible Special Values for the FROMFILE Parameter:
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Select Records and Fields | The second element of the FROMFILE parameter controls prompting of record format names and field names. This prompting cannot be carried out if one of the special values listed above has been specified for the file name parameter.
To prompt the command, type CVTDBF on a command line and press F4. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
From Member File | The third element of the FROMFILE parameter specifies the member's name in the file from which input will be taken. This element must be *FIRST if one of the special values is specified for the file name. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
To prompt the command, type CVTDBF on a command line and press F4.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Example:
CODE
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Here the CVTDBF command is applied to a database file called CUSTFILE which is located in library CUSTLIB. If the command is prompted with F4, a list of fields and record formats from the file on the INCLFLD (Include Fields) and/or RCDFMT (Record Formats) parameters will be shown.
CODE
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Here the CVTDBF command is used to run an SQL statement. The statement's syntax will be checked, and, if valid, executed. The results will be saved in a temporary results table then converted to a stream file in the format specified by the TOFMT parameter. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
TOSTMF (To Stream File) | Determines the path name of the file into which converted data is placed.
For more information about path name specification and more about the IFS, visit the IBM Power Systems Information Center at:
CODE
Here the CVTDBF command is applied to a database file called CUSTFILE. The data is converted to the Excel format and, because TOSTMF (*FROMFILE) is specified, the stream file will be created in the current directory and will be called CUSTFILE.XLS.
CODE
Here again the CVTDBF command is applied to a database file called CUSTFILE. This time the stream file is called customers.xls and will be created in directory customer_data in the root file system. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
TOFMT (To Format) | Selects the format, to which the database file will be converted.
Example:
CODE
Here the CVTDBF command is applied to a database file called ORDERS in order to create a stream file in the Excel format. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
STMFOPT (Stream File Options) | The STMFOPT (Stream File Option) parameter selects the action that CVTDBF should take if the stream file specified with the TOSTMF parameter already exists. This parameter is ignored if the stream file does not already exist.
Example:
CODE
Here the CVTDBF command is applied to a database file called INVOICES. The database file is converted to fixed-format ASCII text and the contents of the database file will be added to the end of the existing file. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
FORMAT (Format Specifications) | This parameter specifies the qualified file name from which the structure of the data being converted will be derived. Slipstream analyzes the metadata available from the file (i.e. its DDS) to determine which fields it contains.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
TORCD (To Record) | This parameter specifies the last relative record in the file to be converted.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SEPCHAR | SEPCHAR specifies the character which will be inserted between the file name and the numerical suffix specified by the TOSTMF parameter. This will be added to the name (without extension) in order to give each stream file created its own name.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SQL (SQL Statement Options) | This parameter only appears if FROMFILE (*SQL) is specified, indicating that the input is to be the result set of an SQL statement. Specify, with this parameter, the SQL statement to be run and define the naming convention to be used.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SQLSRC (SQL Source Options) | The SQLSRC (SQL Source Options) parameter only appears if FROMFILE (*SQLSRC) is specified. This indicates that the input is the result set of an SQL statement held in a source file. This parameter indicates the source file and source member where the SQL statement is stored. Please note:
This parameter contains 2 elements:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SQL Source Member | The second element of the SQLSRC parameter is the name of the source member which contains the SQL statement to be executed.
CODE
This example shows an SQL statement stored in a source file that is run to provide the input to the CVTDBF command. The output from the SQL query will be saved as an Excel file. The SQL statement can be found in a source member CUSTSQL in the source file SQLSRC. See QRYSLT parameter for details on how it relates to record selection performed with the SQL. See SORT parameter for details on how it relates to record sorting performed with the SQL.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
QRYDFN (Query/400-Options) | The QRYDFN (Query/400 Options) parameter only appears if FROMFILE(*QRYDFN) is specified. This indicates that the input is to be the output, created by a Query/400 query. This parameter indicates the name of the Query/400 query object to be run.
|