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:

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:

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: File Name Specify the name of the database file that contains the records which will be converted.
	The possible library values are: *LIBL All libraries in the user and system portions of the job's library list are searched until the first match is found. *CURLIB The current library for the job will be used to locate the database file. If no library is specified as the current li-brary for the job, the QGPL library will be used Library Name Enter a library name that will be searched.Enter a library name that will be searched.
	Possible Special Values for the FROMFILE Parameter: *SQL Runs an SQL statement. The SQL parameter will be used to define the SQL statement, which will be run, and the naming convention to be applied. The SQL statement will be validated, and, if valid, it will be executed and the output saved in a temporary results table, which will then be converted to the stream file format defined by the TOFMT parameter. *SQLSRC Runs an SQL statement held in a source file. The SQLSRC parameter will be used to specify the name of the source file and member where the SQL statement is held. The SQL statement will not be validated until it is executed. *DBEXPORT will create a temporary Query Management (QM) query object from the contents of the source file. This temporary QM query will be executed and the results will be saved in a temporary results table, which will then be converted to the stream file format defined by the TOFMT parameter. *QMQRY Runs a Query Management query. The QMQRY parameter is used to name a QM query object. *DBEXPORT runs the QM query and the results will be saved in a temporary results table, which will then be converted to the stream file format, which was defined by the TOFMT parameter. *QRYDFN Runs a Query/400 query. The QRYDFN parameter is used to name a Query/400 query object. *DBEXPORT runs the Query/400 query and the results will be saved in a temporary results table, which will then be converted to the stream file format, which was defined by the TOFMT 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. *NO Record format and field names will not be prompted. *YES A list of record format and field names from the file specified in the FROMFILE parameter will be prompted. This can be used to indicate which fields and record formats should be included in the stream file and in what order. 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.
	*FIRST The first member will be used. Member_name Enter the name of the member to be used. To prompt the command, type CVTDBF on a command line and press F4.
	Example: CODE CVTDBF FROMFILE(CUSTLIB/CUSTFILE *YES)...
	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. Example:   CODE CVTDBF FROMFILE(*SQL)...
	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. The name entered here may be up to 1,024 characters long. However, the name chosen must be a valid name for the IFS file system into which the stream file is to be created. For example, the shared folders (QDLS) file system only supports file names in the 8.3 format, i.e. a file name up to 8 characters long followed by an optional extension of up to 3 characters. If an invalid file name is chosen, an error will occur and the file will not be saved. A file name, which is suitable for the type of file being created, must be chosen. For example, Excel files should be given the extension .xls so that they are recognized as Excel files by applications such as Excel and Lotus 123. HTML files are normally given as extension of .htm or .html. ASCII fixed text files often have an extension such as .txt, .asc, .prn, or .dat. The following output path/file name variables are possible for dynamic allocation. The variables can be used in any combination. The correct path will be generated automatically: %TMP% This is the default value. The path "/TMP" will be used. %HOME% The "home path"("/HOME/USERNAME") of the operating user will be searched if this option is selected. If the user has no "home path," an error will occur, and the user will be asked to create a home path. %FROMFILE% The name of the output file will be taken from the name of the DB-2 file. %CURDIR% Current directory. %USER% Name of the current user. %DATE% Date DD.MM.YYYY. %YEAR% Current year YYYY. %YEAR2% Current year YY. %MONTH% Current month MM. %MONTHNAME% Name of the current month. %DAY% Current day. %WEEKDAY% Current weekday e.g. "Friday." %TIME% Current time HH:MM:SS. %TIMESTAMP% Current time stamp YYYY-MM-DD-HH.MM.SS.MSMSMS. %DEFAULTPATH% This variable takes the standard value from the *ZIP module's standard settings. to-stmf Enter the complete, relative, or absolute path name for the stream file, which will be created or updated. All directories in this path name must exist already. For more information about path name specification and more about the IFS, visit the IBM Power Systems Information Center at: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/ifs/rzaaxkickoff.htm Example: CODE CVTDBF FROMFILE(CUSTFILE) TOSTMF%CURDIR%/%FROMFILE%) TOSTMF(*XLS)... 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. Example: CODE CVTDBF FROMFILE(CUSTFILE) TOSTMF(‚/customer_data/customers.xls') 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. The following options are possible: *XLS Excel © (BIFF) format. The contents of the database file are converted into columns and rows in an Excel spreadsheet. Column widths are selected appropriately and formatting is implemented to reflect edit codes and edit words associated with the fields in the database file. *CSV Delimited ASCII text (e.g. Comma-separated Variable). The database file contents are converted to delimited ASCII text records. The delimiters used to separate fields and enclose strings are defined on the CSV parameter. This option creates files in the CSV (Comma-separated Variable) format and other similar formats (e.g. tab-separated or blank-separated) for use in spreadsheets and other PC applications. *HTML The contents and attributes of the database file are converted to HTML format, suitable for viewing in a browser such as Internet Explorer or Netscape Navigator. *FIXED Fixed-format ASCII text. The contents of the database file are converted to ASCII text records. Each field is assigned a fixed width so that data for each field always starts and ends in the same position. Each column is separated from the next by a single blank. Example: CODE CVTDBF FROMFILE(ORDERS)... TOFMT(*XLS) 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. The following options are possible: *UNIQUE A unique file name will be created for the output file(s) by adding a numerical suffix to the name specified by the TOSTMF parameter (before the extension). This numerical suffix will be one higher then the highest one of files who have the same name in the same directory. *NONE The command reports an error and the existing file is not changed. This is the default value. *REPLACE The existing file is replaced. *ADD The contents of the database file are added to the end of the existing file. Please note: This option is not allowed in conjunction with TOFMT (*XLS) because it is not possible to append data to an Excel file without making the file unusable. Example: CODE CVTDBF FROMFILE(RECHNUNGEN)... TOFMT(*FIXED) STMFOPT(*ADD) 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. It is possible that this data is held in a "flat" file with no DDS. For example, a file created with CRTPF specifying a record length (RCDLEN) rather than a source member. If this data contains a logical structure, it can still be converted if a second file is created with DDS that reflects the true structure of the data. This second file with the FORMAT parameter tells Slipstream how to convert the data. The following options are possible: *START The first record converted is the first record in the file. Rel_rcd_nbr Specify the relative record number of the first record to be converted.
TORCD (To Record)	This parameter specifies the last relative record in the file to be converted. The following options are possible: *END The last record converted is the last record in the file. Rel_rcd_nbr Specify the relative record number of the last record 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. The following options are possible: *UNDERSCORE An underscore character (_). *NONE No separator is used. Separator Character The Character which will be inserted before the numerical suffix.
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. This parameter contains 2 elements: SQL Statement Enter the SQL statement to be executed. This must be a SELECT statement, or an error will occur. The statement's syntax will be checked prior to execution. Naming Determines the naming convention for the objects in SQL statements. *SYS The system naming convention (library name/file name) is used. *SQL The SQL naming convention (collection-name. table-name) is used. Example: CODE CVTDBF FROMFILE(*SQL) ... TOFMT(*xls) SQL(‚select * from custfile where state = ‚'NY''') This example shows an SQL statement that is run to provide input to the CVTDBF command. The output from the SQL query will be saved as an Excel file. See the QRYSLT parameter for details about how it relates to record selection performed with the SQL parameter. See SORT parameter for details about how it relates to record sorting performed with the SQL parameter.
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: The specified file must exist. The specified member must exist in the file. The file must be a physical source file. The member must not be empty This parameter contains 2 elements: SQL Source file The first element of the SQLSRC parameter is the qualified name of the file which contains the SQL statement to be executed . File Name Specify the name of the source physical file that contains the SQL statement to be executed. Possible values for library are: *LIBL All libraries in the user and system portions of the job's library list are searched until the first match is found. *CURLIB The current library for the job will be used to locate the database file. If no library is specified as the current li-brary for the job, the QGPL library will be used. Library Name Enter a library name that will be searched.
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. [table "794" not found /] Example: CODE CVTDBF FROMFILE(*SQLSRC) ... TOFMT(*xls) SQLSRC(SQLSRC CUSTSQL) 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. This parameter contains 7 elements: Query/400-Object The first element is the qualified name of the Query/400 query object. Query Name Specify the name of the Query/400 query object (object type QRYDFN) which will be run. Query Name Specify the name of the Query/400 query object (object type QRYDFN) which will be run. The possible values for the library are: *LIBL All libraries in the user and system portions of the job's library list are searched until the first match is found. *CURLIB The current library for the job will be used to locate the database file. If no library is specified as the current li-brary for the job, the QGPL library will be used. Library Name Enter a library name that will be searched. Record Selection The second elements controls if the Query/400 Record Selection screen will be prompted. This is only possible if the command is being run interactively. The following options are possible: *NO The Query/400 record selection screen will not be displayed. *YES The Query/400 record selection screen will be displayed if the command is run interactively. See QRYSLT parameter for details on how it relates to record selection performed with the SQL parameter. See SORT parameter for details about on it relates to record selection performed with the SQL parameter.   Query File The third element is the qualified name of the file the Query/400 query processes as its input file . *QRYDFN The input file specified by Query/400 object is used. *QRYFILE A list of the files will be defined by the QRYFILE parameter. If more than one file will be left unconsidered, enter *QRYFILE here and list the files to be used in the separate QRYFILE parameter. File Name The name of the file to be queried. This overrides the file name specified by the Query/400 object. File Name The name of the file to be queried. This overrides the file name specified by the Query/400 object. The possible values of libraries are: *LIBL All libraries in the user and system portions of the job's library list are searched until the first match is found. *CURLIB The current library for the job will be used to locate the database file. If no library is specified as the current li-brary for the job, the QGPL library will be used. Library Name Enter a library name that will be searched. Query File Member The fourth element is the member in the file which the Query/400 query should process as its input file. *FIRST The first member is used. Member Name The name of the member to be used. Output Form Determines the form of output produced by the query. If no value was specified in the query and no value was entered with the command, or if a query name is not specified, *DETAIL is assumed. *RUNOPT If a query definition is used, the output form specified in the query definition will be used when this query is run. If the query contains both detail fields and summary functions, *DBEXPORT will run it in *COMBINED mode (see below). *DETAIL The output form produced by the query is a report containing detail records and summary records if any exist. *SUMMARY The output form produced by the query is a report containing summary records only . *COMBINED If a query contains both detail fields and summary functions, *DBEXPORT will create a file containing both detail lines and summaries. Query Member File If Query/400 produces summary output and that output is directed to an output file, the file created includes two additional columns: the break level specifying the summary level to which the columns relate, and an overflow indicator which flags field overflow if it occurs. This element determines how *DBEXPORT handles the first of the two additional fields (break level). *QRYDFNTXT The value of the break level field (a number representing the break level) is replaced by the corresponding level break text defined for the appro- priate summary level in the Qeury/400 query. *INCLUDE The value of the break level field is included as a column in the output. The column contains a number denoting the summary level to which the other columns relate. *EXCLUDE The break level field is dropped from the output completely. *LEVELBRK The break level field is replaced by a piece of text retrieved from message ID SLP5001 in message file SL_MSGF, combined with the value of the break level field. For level 0 (grand totals) the text is derived from message SLP5002. This text can be modified, if desired, but these modifications will need to be repeated every time a *DBEXPORT PTF or a new release is installed. *SUBTOTAL The break level field is replaced by a piece of text retrieved from message ID SLP5004 in message file SL_MSGF. This is useful if same word should appear each time, e.g. "Subtotal'. For level 0 (grand totals) the text is derived from message SLP5002. This text can be modified, if desired, but these modifications will need to be repeated every time a *DBEXPORT PTF or a new release is installed. The title of the column representing the break level is derived from message ID SLP5003 in message file SL_MSGF . This text can be modified, if desired, but these modifications will need to be repeated every time a *DBEXPORT PTF or a new release is installed.  Include Overflow Indicator? When Query/400 produces summary output and that output is directed to an output file, the file created includes two additional columns: the break level specifying the summary level to which the columns relate, and an overflow indicator which flags field overflow if it occurs. This element determines how *DBEXPORT handles the second of those two additional fields (overflow indicator). *EXCLUDE The overflow indicator field is completely dropped from the output. *INCLUDE The value of the overflow indicator is included as a column in the output . Example: CODE CVTDBF FROMFILE(*QRYDFN) ... TOFMT(*xls) QRYDFN(CUSTQRY *YES) This example shows a Query/400 query call of the CUSTQRY object being run to provide the input to the CVTDBF command. If this command is run interactively, before the query is executed, the Query/400 Record Selection screen will appear in order to enter record selection criteria. The output from the query will be saved as an Excel file. See the QRYSLT parameter for details on how it relates to record selection performed with queries. See the SORT parameter for details on how it relates to record sorting performed with queries.