|
|
|
|
|
|
 |
Slipstream | ||
|
|
Data Sheet |
User Guide |
Slipstream helps IBM iSeries sites deliver high-quality information to their customers and users more quickly and more simply than was possible previously. |
|
Slipstream does this by providing tools for exporting iSeries database tables as PC-format stream files such as Excel spreadsheets, CSV, text and HTML. It also integrates with Query/400, QM/400 and SQl to let you save the output from those functions in Excel and other formats |
|||
|
Do you run queries and still produce the output on paper and have to distribute it through the mail? With Slipstream, without changing your query and with just a single extra command, you you can run the query, save the output as a native Excel spreadsheet and email it to your users or save it on a server for easy access! |
|||
On this page you'll find out:
 | |
| |
| |
| |
| |
| |
|
The iSeries database (UDB for iSeries) is an advanced, function-rich, easily managed relational database allowing large quantities of data to be stored securely and retrieved and updated rapidly and efficiently. However, today, many of our customers and users want to be able to access and manipulate the data held in that database using tools such as spreadsheets, PC databases and Business Intelligence tools. Enabling their customers and users to access data held on the iSeries in the form in which they need it is a challenge that many iSeries sites face on daily basis.
Slipstream provides a innovative solution to this problem. With Slipstream you can create PC-format files from your iSeries database files right on the iSeries itself. You can also turn the laborious process of running a query and getting the output into a spreadsheet into a simple one-step operation.
Let's have a closer look at why your should choose Slipstream.
One frequently used approach to the problem of getting iSeries data into PC applications is to allow users to import data from the iSeries database into PC spreadsheets and databases using tools such as Client Access file transfers and ODBC queries.
However, these solutions have a number of drawbacks, mainly related to the fact that they are “pull” technologies. In other words, a Client Access file transfer or an ODBC query has to be initiated from the PC end and cannot easily be driven from the iSeries. This has a number of unfortunate consequences:
|
Your users have to be in the office to press the buttons. This means that these types of operations typically have to be run during the day. This imposes an unnecessary load on your iSeries and your network when they are at their busiest. It also means that your users waste time preparing data for use. Wouldn’t it be better if the data was ready waiting for them when they arrive in the office? |
|
Your users have to push the buttons at the right time. What happens if the job that creates or updates the data they want hasn’t finished running yet? Do they get yesterday’s data instead? Or might data get lost, corrupted or duplicated? Wouldn’t it be better if you could integrate and schedule the preparation of PC data with your batch processes so that the PC data isn’t created until the iSeries is ready? |
|
Your users have to make sure to push the right buttons! Running regular file transfer and queries can be a repetitive, tedious, error-prone chore. Human beings are fallible and mistakes will inevitably occur from time to time. Wouldn’t it be better if these processes were automated? |
Slipstream Exporter by contrast is a “push” technology. It addresses these problems by providing a set of integrated tools to enable PC-format data to be extracted from the iSeries database entirely on the iSeries, without the need for a PC or any user intervention. This means that the entire process can be fully automated and controlled programmatically, and can be easily integrated with your existing applications and batch schedules.
Slipstream Exporter does this through the simple CVTDBFSTMF (Convert Database File to Stream File) command. This command takes an iSeries database file (physical or logical) and converts it to a stream file in a number of PC file formats. The stream file can be saved anywhere in the IFS (Integrated File System).
Please note that it is not necessary for the stream file to be stored either temporarily or permanent on your iSeries disk: the IFS provides the functionality to write the stream file directly to an NT or UNIX server, if you prefer to store your files there for your users to access. Alternatively, store the stream files locally in the root file system of your iSeries, and anyone with the right authorities and a Client Access network drive attached can read them
See the section on use of the IFS for further details.
Slipstream Exporter can process any of the following as input sources:
|
iSeries physical or logical files |
|
ad hoc SQL query statements |
|
SQL query statements stored in a source file member |
|
Query/400 queries |
|
Query Management (QM/400) queries |
Slipstream Exporter can output the results or contents of these input sources in the form of stream files in any of the following file formats:
|
Excel (either BIFF 8 format compatible with Excel 97 and above or BIFF 5 format compatible with Excel 5 and above). |
|
HTML |
|
Comma Separated Variable (CSV) and other delimited text formats |
|
Fixed text format |
This means that you can now execute a piece of SQL and save the output directly as an Excel spreadsheet, simply by running one command on your iSeries!
Similarly, you could run a query and save the output as an ASCII CSV file, ready to be loaded into Lotus 123. Or if you want to display the latest sales figures on your Intranet, rather than printing a paper report as you might have done before, saved the output from you sales reporting program as a temporary database file, and use Slipstream to convert it to HTML format.
Stream files created by Slipstream can be saved anywhere in your iSeries’s IFS (Integrated File System). This means they can either be held on the iSeries itself (using the root or QDLS file systems), or transferred seamlessly to a Windows server (using the QNTC file system) or to another iSeries (use the QFileSvr.400 file system). See the section entitled “Using the IFS” for details of how to transfer data directly to an NT server when running Slipstream.
Let's have a closer look at these file formats.
You can choose several different formats for your files, depending on your particular requirements. Additional formatting options will be introduced in future versions of the product. For the moment the formats supported are:
If you specify TOFMT(*XLS), CVTDBFSTMF will create an Excel (™) spreadsheet from your database file. The default is to create a BIFF 8 file (compatible with Excel 97, Excel 2000, Excel XP and above). Alternatively, you can choose to create a BIFF 5 file (compatible with Excel 5.0 and above) if your spreadsheet does not support the latest Excel file format.
Files created by CVTDBFSTMF in Excel format can be opened directly by any application with supports the Excel file format, including Microsoft Excel but many others besides, such as Lotus 123 and MS Works spreadsheet.
CVTDBFSTMF 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. CVTDBFSTMF can use any field delimiter you like (by default a comma, but also tabs, blanks etc.) and any string separator you specify (by default a double quote “), allowing you to generate files in the precise format required by your PC application.
If your want to view your data in a browser, such as Netscape® Navigator or Microsoft ® Internet Explorer, CVTDBFSTMF is there to help you.
HTML is the language in which web pages are normally written. CVTDBFSTMF can convert your 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.
CVTDBFSTMF will also simply convert your iSeries 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.
When processing an existing iSeries physical or logical file as input, Slipstream provides a number of additional functions which make delivering high-quality, easily accessible data to your users even 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 |
|
Field inclusion/exclusion – you can include all fields in the file being processed, specify a list of fields to include or a list of fields to exclude |
|
Field sorting – you can specify the order in which fields are to be presented by listing them in the order you want them |
|
Formatting – date, time and number formats can be managed and edit codes and edit words are reflected in Excel formatting. |
Let's have a closer look at what Slipstream can do for your business.
Slipstream revolves around the CVTDBFSTMF (Convert Database File to Stream File) command, which can take any iSeries database file (i.e. DB2 UDB for iSeries file or table) and convert it to formats that can be used by popular PC applications such as spreadsheets, databases and browsers.
Here are a few possible ways Slipstream can help you deliver data to your users more efficiently and reliably.
Still sending reports to your users on paper? Still distributing data to customers via snail mail?
Wouldn’t your users rather have the same information in electronic form so they can load it into their PC spreadsheets without having to re-key it? Slipstream can help you modify your applications so you can deliver data to your users in the form they need. All that is needed is that you change your programs to output to a database file rather than to a spooled file. You can then use Slipstream to create an Excel spreadsheet, CSV or HTML or fixed text file from your database file
Files created by Slipstream can be e-mailed to users, colleagues and customers, rather than sent out on paper, making their lives easier whilst saving you money and simplifying your business processes.
Imagine you have Excel power-users in your finance department who need to analyze your company’s sales figures from the previous week each Monday morning. To get the information from the sales database into Excel, your users could perform a Client Access file transfer or run an ODBC query. “Isn’t that the easiest way?” you might ask. However, Slipstream offers some significant advantages over these methods:
| Data is ready for your users as soon as they want it. Run your conversions overnight and in the morning your users simply need to connect to the iSeries and open a file from the IFS or a PC server. They don’t need to waste time running file transfers or ODBC queries. |
| Data can be prepared when convenient to you. Conversions can be easily integrated with your normal overnight or weekend batch jobs, and therefore run out of hours when the load on your system and network is lowest. There’s no need for a PC at all and no need for complicated, error-prone iSeries-to-PC communications to trigger file transfers or ODBC queries. |
| Conversions don’t take place until the data is ready. Run the conversion step immediately after the step that creates the data to ensure that the two occur in the right sequence. |
| Conversion will be done correctly every time, automatically. Since the conversion parameters are built into your applications, you don’t have to rely on your users to run the transfer or the query correctly each day or each week, and they have to remember what buttons to press and which selections to make each time. |
For example, rather than having your users run ODBC queries against the sales database on a Monday morning, you could run a job over the weekend to extract information about sales made the previous week. This could be as simple as running a Query/400 query. With Slipstream you can run the query and save the output as an Excel, CSV file, HTML or ASCII text file in your IFS. Your users only need a network drive assigned to the right iSeries and the necessary permissions to access the data. They can simply load their spreadsheet application on Monday morning, open the file previously created using CVTDBFSTMF, and away they go.
The beauty of this approach is that the iSeries controls, schedules, and manages the creation of the PC-format data entirely. It saves your users time and effort, saves you worries about whether the users will get their file transfer right or run their ODBC query at the wrong time, and saves the iSeries from unnecessary workload during office hours.
Often, many of your users will want the same data. This can often mean that different users will be running the same or very similar queries or file transfers against your database all in 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?
Alternatively, you could publish these files on your Web site for customers worldwide to see, or on a secure Intranet or Extranet.
Stream files created by Slipstream can be saved anywhere in your iSeries’s IFS (Integrated File System).
OS/400 V5R1M0 or above.
4 Mb of iSeries disk space.
Slipstream Exporter is the professional version of the popular CVTTOSTMF utility, previously published in News/400 magazine.
|
Output to native Excel format and HTML |
|
Major performance improvements (Slipstream Exporter normally runs at least twice as fast as CVTTOSTMF) |
|
Record selection |
|
Record sorting |
|
Field selection and sequencing |
|
The ability to run a piece of SQL or a query and save the output as a stream file |
|
Options to control date formats, time formats, numeric field editing, authorities and CCSIDs |