Easysoft SQI-Zortec System Z Driver User Guide - Demonstration
Worked example of connecting to System Z data
This section demonstrates connecting to local System Z data from an ODBC-compliant application on your Windows or Unix machine.
If you are implementing cross-platform access to your System Z data using the Easysoft ODBC-ODBC Bridge or the Easysoft JDBC-ODBC Bridge, please refer to the documentation provided with those products for an example of connecting to remote data.
Demonstration on Windows
This section provides a worked example of using Microsoft Access to connect to the sample System Z data provided with the Easysoft SQI-Zortec System Z Driver and assumes that:
To obtain a list of customers and display the results in Microsoft Access:
1. Run Microsoft Access.
2. Create a new blank database.
3. Display the Tables tab on the database window.
4. Right-click in the empty window, then select Link Tables to display the Link dialog box.
5. Select ODBC Databases () from the Files of Type drop-down list box to display the Select Data Source dialog box.
6. Click the Machine Data Source tab.
7. Select the data source that connects to the sample data (the default name for this data source is Sample_SystemZ, but you may have given it an alternative name).
8. Click OK to display the Link Tables dialog box.
9. Click Select All to link all the tables into the database, then click OK.
After a few moments, the tables are listed in the Tables tab of the database window in Microsoft Access.
10. Click the Queries tab in the database window, then click New to create a new query.
11. On the New Query wizard, click Design View and then click OK.
12. On the Show Table dialog box, select the Z_SUPPLIER table then click Add to add the table to the Select Query window.
13. Select the Z_PRODSUPP table then click Add.
14. Select the Z_PRODUCT table then click Add.
15. Click Close to close the Select Table dialog box.
16. Delete the two join lines between the tables by selecting each in turn and pressing <Delete>.
If you resize the tables until you can see all the column names in full, the Select Query window will look something like:
Figure 26: The Microsoft
Access Select Query window table details
17. Create the following joins:
- From SUPPLIER-ID in the Z_SUPPLIER table to PRODSUPP-SUPPLIER-ID in the Z_PRODSUPP table
- From PRODUCT-ID in the Z_PRODUCT table to PRODSUPP-PRODUCT-ID in the Z_PRODSUPP table
Figure 27: The Microsoft
Access Select Query window table join details
18. In the Select Query window, drag each of the following columns in turn from the Z_SUPPLIER and Z_PRODUCT tables in the upper half of the window and release them in the columns in the lower half of the window:
The Select Query window will now look something like:
Figure 28: The Microsoft
Access Select Query window table column details
19. Click the Close box to close the Select Query window. When asked if you want to save the query, click Yes. The Save As dialog box is displayed.
20. Enter a name for the query and then click OK.
The query is now listed on the Queries tab of the database window.
21. Double-click the query to open it.
A list of suppliers and the products they supply is displayed.
22. Close Microsoft Access unless you want to continue using it for another task.
Demonstration on Unix
This section provides a worked example of using SQL queries to connect to the sample System Z data provided with the Easysoft SQI-Zortec System Z Driver.
This demonstration uses the isql script described in "Querying data sources" on page 59. You may prefer to skip this demonstration if you have already worked through the instructions in that section.
This worked example assumes that:
- you have a data source connecting to the sample data. This sample data source is created automatically during the installation. If you do not have this data source set up, refer to "Creating additional data sources on Unix" on page 68 for details of how to set up a data source.
- the dsn= attribute in the isql script is set to the sample data source ('sample_systemz' by default), and the uid= and pwd= attributes are set to the name and password of an existing user who has full access to all the tables in the sample data source.
To query the sample data source using the iSQL script:
1. Change into the usr/local/easysoft/sqi/systemz directory.
If an 'invalid user' or 'invalid password' error is displayed, check that the correct user name and password are specified in the script.
If a line beginning "conn =" is displayed, then the connection has been made and you can type an SQL statement to query the data source.
3. Type the following to display a list of tables in the data source:
select * from info_schema.tables;
4. Type the following, on one continuous line, to display a list of supplier names and their locations:
select "supplier-name", "supplier-town"
5. Type the following, on one continuous line, to display a list of product names and their prices:
select "product-name", "product-price"
6. Type the following, on one continuous line, to display a list of suppliers and the products that they stock:
select "supplier-name", "supplier-town",
from z.supplier, z.prodsupp, z.product
If the returned data scrolls out of view, press <Pause> to pause the scrolling, then press <Enter> to view the remainder of the list.
7. To finish your sql session, press <Enter> to return to the system prompt.