8 Copyright IBM Corporation, 2008. All Rights Reserved.
System i Access for Windows:
PC
System i
Transfer Request (.DTF, .DTT)
Data Physical files and Source Physical files PC Files Data Transfer File Description File (FDF) Database Server
Data Transfer from System i
CWBTF.EXE (The GUI) uploads and downloads data interactively
CWBTFCAI.DLL (The Excel Add-in) moves data between database files and the
active worksheet so users can take advantage of the facilities of Microsoft® Excel
Database upload request Sample
1) Specify the system name
2) Specify the source database file 3) Select your data
5) Set options that are specific to the output device 4) Select the output device
6) Provide the name for your PC file
7) Transfer your data
The Details panel has options that are specific to the selected output device
1) Start the Database file wizard
2) Select the PC file 3) Select the file type
4) Specify the path for the FDF
5) Set the Data options if needed
6) Scan the PC file
7) Review the scanning result
8) Specify the system name
9) Specify the database file name
10) Specify the file text if needed
11) Confirm the create options
12) The final panel confirms the database file has been created successfully
13) Finally, transfer your data
Key points about the System i Database file wizard:
•
Creating the table and FDF does not transfer the data
•
Plan ahead by increasing lengths if necessary
1) Start the download function Data Transfer Excel Add-in toolbar
Transfer Requests can be used
The location for the data in the worksheet can be customized
2) Start the download wizard 3) Specify the system name
5) Select your data 4) Specify the source database file
Data format options and other download setting are available at this panel
6) Create a download request if needed
7) Transfer your data
1) Select your data
2) Start the upload function
The first row can be used as column heading, like in this example
3) Start the upload wizard
4) Specify how you want your data stored These options create a database file and an FDF file
5) Specify the path and name for FDF
A database file and an FDF file must already in order to use these options
6) Set the upload options if needed
7) Scan you data
8) Review the scanning result
Field options such us CCSID, length, default value, etc can be set up at the Details panel
9) Specify the system name
10) Specify the database file name
11) Specify the file description text if needed
12) Verify and confirm the upload settings
13) Create the upload request if needed 14) Finally, upload your data
1) Key in the following command 2) Signon to your system if needed 3) Check the download results
All of the parameters are explained here
1) Key in the following command 2) Signon to your system if needed 3) Check the upload results
All of the parameters are explained here
1) Key in the following command
2) Check the results
All of the parameters are explained here The userid and password are required parameters
…
Dim dlr As New cwbx.DatabaseDownloadRequest Dim mySystem As New cwbx.AS400System
mySystem.Define “MySystemName" mySystem.UserID = “MyUserID" mySystem.Password = “MyPassword" Set dlr.System = mySystem
dlr.AS400File = “QIWS/QCUSTCDT" dlr.pcFile = "c:\mycustomers.xls" dlr.pcFile.FileType = cwbdtBIFF8 dlr.Download
…
A simple Visual Basic program for running a download using the low level DatabaseDownloadRequest object A download request is declared
A system object is declared The system object’s properties are initialized The download request’s properties are initialized
The actual data download takes place
•
Ability to open downloaded files by clicking a button
•
New COM-based Excel Add-in
•
Ability to set default data types
•
Support for processing list files in RXFERPCB.EXE
•
Support for parameter markers on download operations
•
Support for SQL Table Names
•
Support for DECFLOAT data type
Click here to open the file with its corresponding application
Excel Add-in Upload function Excel Add-in Download function
Same toolbar, same appearance, same functionality but multiple transferences can run concurrently
A default data type for alphabetic data
A default data type for numeric data
A default CCSID can be specified
These customizations can be persistent
SQL table names can be longer and more descriptive than System names
The Data Transfer GUI now displays SQL table names by default but System names can be displayed instead
Parameter markers can now be inserted in the WHERE clause
Customized labels can be created for every parameter marker
Users can tell what data corresponds to every parameter marker
Parameter markers are replaced with the provided values so the download can be performed
DECFLOAT is a new data type that Data Transfer supports
Is DECFLOAT an alphabetic or a numeric data type?
DECFLOAT is a numeric data type with special characteristic
•
They can be potentially huge and can easily exceed the capacity of any standard data
type so, sometimes they have to be stored and handled as strings.
•
There are special DECFLOAT values that cannot be stored as numeric (Infinity, NaN,
etc.)
•
Small values can be stored an handled as ordinary numeric values
These cells actually contain strings. No arithmetic operation can be performed with these values but Data Transfer can upload them as DECFLOAT to a database file, where arithmetic operations can be performed
By default, DECFLOAT values are stored as strings.
Users can select to store them as numeric but conversion errors may result if a value exceeds the capacity of the numeric data type
Transfer requests can be configured to run automatically
Data Transfer closes when the completion dialog box is closed
Automatic requests run by double clicking the transfer request file (DTT or DTF) or any shortcut to it.
Tips:
• IT administrators can put automatic transfer request in a read only location to let the users run them easily and protect the requests from unauthorized modifications
• The batch applications can be used to create a batch file and process multiple request by clicking an icon
System i Access for Windows does not provide a scheduler program but Window’s Task Scheduler can be used
1) Go to the control panel and start the Task Scheduler wizard
2) Specify the path to your transfer request
3) Verify the task has been created
Tips:
• The completion message can be disabled so the transfer request runs completely behind the scenes
• The batch interface can be used to schedule multi-request transferences
The Excel Add-in upload function allows to convert Excel date and time cells, to System i date or time data types, and allows numeric data in character columns to be converted to character data
Enable the option for converting date and time cells to System i DATE and TIME data in order to upload these data correctly
Enable the option for allowing numeric data in character columns to be converted to character data in order to upload these data correctly
Tips:
• If the format of the cells containing numeric data is changed to “Text”, the Excel Add-in treats the data as character data
HTML File support - Updating a web server on System i5
Workstation running Data Transfer System i running a Web server
Client PC’s
1) Data Transfer downloads data from a database file and creates an HTML file
2) The HTML file is sent to the Web Server
3) Clients view the data on a Web browser
HTML File support - Updating a web server on another machine
Workstation running Data Transfer System i
Client PC’s
1) Data Transfer downloads data from a database file and creates an HTML file
2) The HTML file is sent to the Web Server
3) The Clients view the data on a Web browser
Web server
How can I create an HTML file using Data Transfer? 1) Select HTML as output device
2) Set the HTML specific options if needed
3) Transfer your data
Is it possible to use customized HTML templates?
Data Transfer can insert tabular data into a pre-formatted HTML document at a specified location. The location is defined by an embedded template tag.
The template document may contain graphics, links, frames, and any other HTML elements you provide.
Users must tell Data Transfer what is the embedded tag to look for and replace with the data
The HTML template must include the embedded tag … <HTML> <HEAD> <TITLE>Sample HTML Code</TITLE> </HEAD> <BODY> <H1>Customer Data</H1> <!-- TABLE1 --> </BODY> </HTML> … Tips:
• Transferring the DB2 UDB for System i DATALINK type to your HTML file will produce active links
• Data Transfer allows to automatically split up large transfers into linked HTML files that are more manageable by the web browsers
•
Data Transfer relies on System i5 DB2 database server to store or retrieve data from
database files
•
Data Transfer retrieves and downloads data from database files by having System i5
DB2 database server run SQL queries that users create
•
Data Transfer provides a GUI for users to create their SQL queries easily
Data Transfer from System i Excel Add-in download function
Data Transfer from System i and the Excel Add-in download function, allow to use the query builder. Click the Data Options button to bring up the Change Data Options panel
Enter SQL-like statement directly into the edit panels or …
click the Details button to bring up the query builder
Double click on field names in the field list to add them to the Select clause
Functions such as SUM may also be used to return functional results
Specify conditions by building a Where clause
Double click on a field name then specify a test condition by double clicking on a test operator
Tip:
• This is the panel where you can insert parameter markers
An Order By clause may be specified to order the data
Double click on a field name and specify the order criterion
Using the Data Transfer query builder
Using the Data Transfer query builder
A Native SQL interface is provided for power users or for situations where the Data Transfer format does not provide enough functionality
After choosing to process your statement as Native SQL, the Data Options button activates a Native SQL panel
The Native SQL interface allows you to type in a free form SELECT statement. A list of files and columns in those files are provided to help you build your statement
Setting up your library list
Setting the desired signon mode and security options
Tip:
• By using the Windows user and password as signon mode, automatic transfer request and scheduled transfer requests signon behind the scenes so user don’t have to do it interactively
•
An optional initialization file users can use to control some Data Transfer
options that are not controllable through the GUI
•
Described at:
http://www-1.ibm.com/support/docview.wss?uid=nas1352f81ff9d0d4c86862565c2007cae44
Example:
ActiveX? Automation Objects? So what's this?
ActiveX Automations are re-usable objects that reside on your Windows PC. Many times
they can be used to run an application by "remote" with a program or script
So what can they do for me?
OK, How do I use them?
ActiveX automations are supported by many programming languages including:
•
Visual Basic
•
Visual Basic for Applications (used by Microsoft Office)
•
Visual Basic Script (used in web pages and the PC5250 emulator)
•
C++
•
Many other applications and development environments
You must write program code to use these objects. Or allow some development tool
to write the code for you
What Automations are available for Data Transfer?
•
High level Automation
Using a high level object: DatabaseTransfer
These two lines of Visual Basic (VB) code can be used to run a download:
Dim dt As New cwbx.DatabaseTransfer
dt.Download "mysys", "qiws/qcustcdt", "c:\myfile.xls", cwbdtBIFF5
To do an upload:
dt.Upload "mysys", "cwbxtest/qcustcdt", "c:\qcustcdt.txt", "c:\qcustcdt.fdf“
To run a saved request:
Using a high level object: DatabaseTransfer
The DatabaseTransfer automation object also contains properties you can
query or set for the transfer request:
•
Errors - for query only. A standard collection of error messages. Messages get put
into this collection while the request is running
•
Password - Allows you to set the password for the System i5 connection necessary for
the transfer
•
TransferResults - Allow you to get the number of rows transferred, return codes, and
error and warning locations.
Do you need more information?
Additional Information on the System i Access for Windows ActiveX automation objects
can be found in the System i Access for Windows Toolkit. This information can be found
under the ActiveX section of the Database portion of the Toolkit documentation.
Sample programs are available at:
© IBM Corporation 1994-2007. All rights reserved.
References in this document to IBM products or services do not imply that IBM intends to make them available in every country.
Trademarks of International Business Machines Corporation in the United States, other countries, or both can be found on the World Wide Web at http://www.ibm.com/legal/copytrade.shtml.
Intel, Intel logo, Intel Inside, Intel Inside logo, Intel Centrino, Intel Centrino logo, Celeron, Intel Xeon, Intel SpeedStep, Itanium, and Pentium are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.
IT Infrastructure Library is a registered trademark of the Central Computer and Telecommunications Agency which is now part of the Office of Government Commerce. ITIL is a registered trademark, and a registered community trademark of the Office of Government Commerce, and is registered in the U.S. Patent and Trademark Office. UNIX is a registered trademark of The Open Group in the United States and other countries.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both. Other company, product, or service names may be trademarks or service marks of others.
Information is provided "AS IS" without warranty of any kind.
The customer examples described are presented as illustrations of how those customers have used IBM products and the results they may have achieved. Actual environmental costs and performance characteristics may vary by customer.
Information concerning non-IBM products was obtained from a supplier of these products, published announcement material, or other publicly available sources and does not constitute an endorsement of such products by IBM. Sources for non-IBM list prices and performance numbers are taken from publicly available information, including vendor announcements and vendor worldwide homepages. IBM has not tested these products and cannot confirm the accuracy of performance, capability, or any other claims related to non-IBM products. Questions on the capability of non-IBM products should be addressed to the supplier of those products.
All statements regarding IBM future direction and intent are subject to change or withdrawal without notice, and represent goals and objectives only.
Some information addresses anticipated future capabilities. Such information is not intended as a definitive statement of a commitment to specific levels of performance, function or delivery schedules with respect to any future products. Such commitments are only made in IBM product announcements. The information is presented here to communicate IBM's current investment and development activities as a good faith effort to help with our customers' future planning.
Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment. The actual throughput or performance that any user will experience will vary depending upon considerations such as the amount of multiprogramming in the user's job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve throughput or performance improvements equivalent to the ratios stated here.
Prices are suggested U.S. list prices and are subject to change without notice. Starting price may not include a hard drive, operating system or other features. Contact your IBM representative or Business Partner for the most current pricing in your geography.