313 lines
21 KiB
HTML
313 lines
21 KiB
HTML
<?xml version="1.0" encoding="UTF-8"?>
|
|
<!DOCTYPE html
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
|
<html lang="en-us" xml:lang="en-us">
|
|
<head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
|
<meta name="security" content="public" />
|
|
<meta name="Robots" content="index,follow" />
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
|
<meta name="DC.Type" content="reference" />
|
|
<meta name="DC.Title" content="Database considerations" />
|
|
<meta name="abstract" content="The following considerations apply to using the iSeries Access for Web database functions in a Web application server environment." />
|
|
<meta name="description" content="The following considerations apply to using the iSeries Access for Web database functions in a Web application server environment." />
|
|
<meta name="DC.subject" content="Run Sql, Output types, XML output, creating, Run SQL, Run SQL, HTML output, HTML output, PDF output, PDF, font settings, copy data to table, XML considerations, XML" />
|
|
<meta name="keywords" content="Run Sql, Output types, XML output, creating, Run SQL, Run SQL, HTML output, HTML output, PDF output, PDF, font settings, copy data to table, XML considerations, XML" />
|
|
<meta name="DC.Relation" scheme="URI" content="rzammhdatabase.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="rzammhcustomize.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="../rzahh/jdbcproperties.htm" />
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 2003, 2006" />
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 2003, 2006" />
|
|
<meta name="DC.Format" content="XHTML" />
|
|
<meta name="DC.Identifier" content="rzammdatabaseconsiderations" />
|
|
<meta name="DC.Language" content="en-us" />
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
|
<!-- US Government Users Restricted Rights -->
|
|
<!-- Use, duplication or disclosure restricted by -->
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
|
<title>Database considerations</title>
|
|
</head>
|
|
<body id="rzammdatabaseconsiderations"><a name="rzammdatabaseconsiderations"><!-- --></a>
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|
<h1 class="topictitle1">Database considerations</h1>
|
|
<div><p>The following considerations apply to using the iSeries™ Access
|
|
for Web database functions in a Web application server environment.</p>
|
|
<div class="section">The database considerations can be grouped into these functional
|
|
categories: <ul><li><a href="#rzammdatabaseconsiderations__connections">Database connections</a></li>
|
|
<li><a href="#rzammdatabaseconsiderations__outputtypes">Run SQL: Output
|
|
types</a></li>
|
|
<li><a href="#rzammdatabaseconsiderations__parmmarkers">Run SQL: Prompted
|
|
statements</a></li>
|
|
<li><a href="#rzammdatabaseconsiderations__copydatatotable">Copy data to
|
|
table</a></li>
|
|
<li><a href="#rzammdatabaseconsiderations__importquery">Import query</a></li>
|
|
</ul>
|
|
<dl><dt class="dlterm" id="rzammdatabaseconsiderations__connections"><a name="rzammdatabaseconsiderations__connections"><!-- --></a><img src="./delta.gif" alt="Start of change" />Database connections<img src="./deltaend.gif" alt="End of change" /></dt>
|
|
<dd><img src="./delta.gif" alt="Start of change" />iSeries Access
|
|
for Web makes JDBC calls to access database tables. By default, the IBM<sup>®</sup> Toolbox for Java™ JDBC
|
|
driver is used to establish a driver manager connection to the iSeries server
|
|
running iSeries Access
|
|
for Web. The customize function provides support for defining additional database
|
|
connections. Additional connections can be defined to access different iSeries servers,
|
|
to specify different driver settings, or to use different JDBC drivers. If
|
|
a different JDBC driver is used in a single sign-on (SSO) environment, any
|
|
required authentication values must be set on the JDBC URL. <div class="p">The customize
|
|
function also provides support for defining data source connections. This
|
|
support is only available in the WebSphere<sup>®</sup> application server environment.
|
|
Version 4 data sources are not supported. If iSeries Access for Web is configured
|
|
to use basic authentication to prompt for the iSeries user profile and password, these
|
|
user and password values are also used to authenticate the data source connection.
|
|
If iSeries Access
|
|
for Web is running in an SSO environment, the data source must have a component-managed
|
|
authentication alias configured. <div class="note"><span class="notetitle">Note:</span> iSeries Access for Web is tested using
|
|
the IBM Toolbox
|
|
for Java JDBC
|
|
driver. Using a different driver might work, but this is an untested and unsupported
|
|
environment.</div>
|
|
For a list of the different connection properties that
|
|
are recognized by the JDBC driver, refer to the "IBM Toolbox for Java JDBC properties" topic.</div>
|
|
<img src="./deltaend.gif" alt="End of change" /></dd>
|
|
<dt class="dlterm" id="rzammdatabaseconsiderations__outputtypes"><a name="rzammdatabaseconsiderations__outputtypes"><!-- --></a>Run SQL: Output types</dt>
|
|
<dd>The supported output types for returning SQL statement result data are:
|
|
<ul><li>Preview</li>
|
|
<li>Comma separated value (.csv)</li>
|
|
<li>Data interchange format (.dif)</li>
|
|
<li><a href="#rzammdatabaseconsiderations__xmloutput">Extensible markup
|
|
language (.xml)</a></li>
|
|
<li><a href="#rzammdatabaseconsiderations__htmloutput">Hypertext markup
|
|
language (.html)</a></li>
|
|
<li><a href="#rzammdatabaseconsiderations__execel123output">Lotus<sup>®</sup> 1-2-3<sup>®</sup> version
|
|
1 (.wk1)</a></li>
|
|
<li><a href="#rzammdatabaseconsiderations__execel123output">Microsoft<sup>®</sup> Excel
|
|
3 (.xls)</a></li>
|
|
<li><a href="#rzammdatabaseconsiderations__execel123output">Microsoft Excel
|
|
4 (.xls)</a></li>
|
|
<li><a href="#rzammdatabaseconsiderations__execelxmloutput">Microsoft Excel
|
|
XML (.xml)</a></li>
|
|
<li><img src="./delta.gif" alt="Start of change" /><a href="#rzammdatabaseconsiderations__ods">OpenDocument
|
|
Spreadsheet (.ods)</a><img src="./deltaend.gif" alt="End of change" /></li>
|
|
<li><a href="#rzammdatabaseconsiderations__pdfoutput">Portable Document
|
|
Format (.pdf)</a></li>
|
|
<li>Text, plain (.txt)</li>
|
|
<li>Text, tab delimited (.txt)</li>
|
|
</ul>
|
|
<dl><dt class="dlterm" id="rzammdatabaseconsiderations__xmloutput"><a name="rzammdatabaseconsiderations__xmloutput"><!-- --></a>Extensible markup language (.xml)</dt>
|
|
<dd>The XML output generated by Run SQL is a single document containing both
|
|
an embedded schema and results from the SQL query. The embedded schema is
|
|
compliant with the W3C Schema Recommendation dated May 2, 2001. The schema
|
|
contains meta information for the query results portion of the document. Contained
|
|
in the schema is data type information, limits on data, and document structure.<p>Current
|
|
XML parser implementations do not support validation using the embedded approach.
|
|
Many parsers, including SAX and DOM implementations, require independent documents
|
|
for the schema and content to do validation. To achieve schema validation
|
|
with an XML document produced by Run SQL, the document must be restructured
|
|
into individual data and schema documents. The root elements must also be
|
|
updated to support this new structure. Visit the <a href="http://www.w3.org" target="_blank">World Wide Web Consortium's Web site</a> for additional
|
|
information about XML Schema.</p>
|
|
<p>The query results portion of the XML document
|
|
contains the data returned from the query in a structured row and column fashion.
|
|
This data may be easily processed by other applications. If more information
|
|
about the data contained in this topic is required, reference can be made
|
|
to the document schema. </p>
|
|
</dd>
|
|
<dt class="dlterm" id="rzammdatabaseconsiderations__htmloutput"><a name="rzammdatabaseconsiderations__htmloutput"><!-- --></a>Hypertext markup language (.html)</dt>
|
|
<dd>When the HTML output type is being used, the results are displayed in
|
|
the browser. To save the results to a file, the browser save function can
|
|
be used. Another option is to save the SQL request and to redirect the results
|
|
to a file when the request is run. With Internet Explorer, right-click the
|
|
Run link and choose the Save Target As option. With Netscape Navigator, hold
|
|
down the shift key while clicking the Run link.<p>If the HTML rows per table
|
|
value is set, Run SQL will display the results in a paged list, similar to
|
|
Preview, instead of returning a single HTML page.</p>
|
|
</dd>
|
|
<dt class="dlterm" id="rzammdatabaseconsiderations__execel123output"><a name="rzammdatabaseconsiderations__execel123output"><!-- --></a>Microsoft Excel/Lotus 1-2-3</dt>
|
|
<dd>The supported file formats for Microsoft Excel and Lotus 1-2-3 are not
|
|
the newest types supported by these applications. Since the additional capabilities
|
|
of the newer types are not likely to be needed for retrieving data from a
|
|
database, this should not result in a loss of functionality. By supporting
|
|
the older versions of these file types, compatibility can be retained for
|
|
the older versions of these applications. A newer version of the application
|
|
can be used to save the results to a file in a newer format.<div class="note"><span class="notetitle">Note:</span> The file
|
|
in the new format is not compatible with <a href="#rzammdatabaseconsiderations__copydatatotable">Copy
|
|
data to table</a>.</div>
|
|
</dd>
|
|
<dt class="dlterm" id="rzammdatabaseconsiderations__execelxmloutput"><a name="rzammdatabaseconsiderations__execelxmloutput"><!-- --></a>Microsoft Excel XML (.xml)</dt>
|
|
<dd>The Microsoft Excel XML file format is only supported
|
|
by Microsoft Excel
|
|
2002 or newer versions of the application. Microsoft Excel 2002 is part of Microsoft Office
|
|
XP.</dd>
|
|
<dt class="dlterm" id="rzammdatabaseconsiderations__pdfoutput"><a name="rzammdatabaseconsiderations__pdfoutput"><!-- --></a>Portable document format (.pdf)</dt>
|
|
<dd>The PDF file format represents your SQL data as it would appear on a page.
|
|
The amount of data that can fit on a page depends on the page size, the page
|
|
orientation, and the margin sizes. A very large number of columns can result
|
|
in an unusable PDF document. In some cases, the Adobe Acrobat Reader plug-in
|
|
cannot load a file like this into the browser. As an alternative, you can
|
|
break the request into multiple queries, which return subsets of the columns,
|
|
or you can choose a different output type.<p>Using the output settings, you
|
|
can customize the selection of fonts used for the various parts of the PDF
|
|
document. You can embed the chosen fonts into the document, rather than installing
|
|
them on the computer used to view the document. Embedding fonts in the document
|
|
increases the document size.</p>
|
|
<p>The character encoding used to represent
|
|
text is also a PDF output option. If the font is not able to represent a character
|
|
in the encoding, the character is left blank or another indicator character
|
|
is used to show the character cannot be displayed. You should choose font
|
|
and character set values which are capable of representing all characters
|
|
in the data to display.</p>
|
|
<p>By default, Run SQL supports the standard PDF
|
|
fonts and the Adobe Asian fonts for building PDF output. Since the standard
|
|
fonts are required to be available with any PDF viewer, there is no need to
|
|
embed them in the PDF document. Adobe provides a set of Asian font packs
|
|
for displaying text containing Simplified Chinese, Traditional Chinese, Japanese,
|
|
or Korean characters. Run SQL supports creating documents with these fonts,
|
|
but it does not support embedding these fonts in the document. If these fonts
|
|
are used, the appropriate font pack needs to be installed on the computer
|
|
used to view the document. These font packs can be downloaded from <a href="www.adobe.com" target="_blank">Adobe's Web site</a>.</p>
|
|
<p>Additional
|
|
fonts can be added to the available fonts list, using the "Additional PDF
|
|
font directories" Customization setting. The supported font types are:</p>
|
|
<ul><li>Adobe Type 1 fonts (*.afm) <p>In order for Type 1 fonts to be embedded
|
|
into a document, the Type 1 font file (*.pfb) needs to be in the same directory
|
|
as the font metrics file (*.afm). If only the font metrics file is available,
|
|
the document can be created with the font, but the computer used to view the
|
|
document needs to have the font installed. Type 1 fonts only support single-byte
|
|
encoding.</p>
|
|
</li>
|
|
<li>TrueType fonts (*.ttf) and TrueType font collections (*.ttc) <p>Embedding
|
|
TrueType fonts and TrueType font collections is optional. When a TrueType
|
|
font is embedded, only the portions of the font needed to represent the data
|
|
are embedded. The list of available character set encodings is retrieved from
|
|
the font file. In addition to the retrieved encodings, the multilingual "Identity-H"
|
|
encoding can be used. When this encoding is used, the font is always embedded
|
|
into the document. You can embed TrueType fonts, which support double-byte
|
|
character sets, as an alternative to the Adobe Asian fonts. This generates
|
|
a larger document, but the computer used to view it does not need to have
|
|
the font installed.</p>
|
|
<p>Run SQL supports building PDF documents with bi-directional
|
|
data, if the locale of the current request is Hebrew or Arabic.</p>
|
|
</li>
|
|
</ul>
|
|
</dd>
|
|
<dt class="dlterm" id="rzammdatabaseconsiderations__ods"><a name="rzammdatabaseconsiderations__ods"><!-- --></a><img src="./delta.gif" alt="Start of change" />OpenDocument Spreadsheet (.ods)<img src="./deltaend.gif" alt="End of change" /></dt>
|
|
<dd><img src="./delta.gif" alt="Start of change" />The OpenDocument spreadsheet documents created by iSeries Access for Web conform to the
|
|
Oasis Open Document Format for Office Applications (OpenDocument) 1.0 specification.<p>Date
|
|
and time values are stored in date and time format only if the ISO date and
|
|
time format options are chosen. Otherwise, they are stored as text values.</p>
|
|
<img src="./deltaend.gif" alt="End of change" /></dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="dlterm" id="rzammdatabaseconsiderations__parmmarkers"><a name="rzammdatabaseconsiderations__parmmarkers"><!-- --></a>Run SQL: Prompted statements</dt>
|
|
<dd>Run SQL supports SQL statements containing parameter markers. There are
|
|
two ways to create this type of statement. The first option is through the
|
|
SQL Wizard. The SQL Wizard supports parameter markers for condition values.
|
|
In addition to the option of specifying the condition values as part of the
|
|
request, the SQL wizard provides the option to prompt for condition values
|
|
when the request is run. The SQL wizard handles building the page to prompt
|
|
for the condition values as well as building the SQL statement containing
|
|
parameter markers.<p> The second way to create an SQL statement with parameter
|
|
markers is to manually enter the statement using Run SQL. This option requires
|
|
the request to be saved. The request cannot be run directly from Run SQL
|
|
or with the Run link from My Requests. The Run SQL Request (iWADbExec) URL
|
|
interface must be used to pass values for the parameter markers. One URL
|
|
parameter must be specified for each parameter marker in the statement. The
|
|
URL parameters must be named iwaparm_x, where x is the parameter index. iwaparm_1
|
|
is used for the first parameter marker, iwaparm_2 is used for the second parameter
|
|
marker, and so on. For example: </p>
|
|
<pre> http://server:port/webaccess/iWADbExec?request=promptedRequest&iwaparm_1=Johnson&iwaparm_2=500</pre>
|
|
<p>One way to invoke the Run SQL Request (iWADbExec) URL interface to pass
|
|
values for parameter markers is to use an HTML form. Input fields can be
|
|
used to prompt the user for the values. The names of these fields must follow
|
|
the URL parameter naming convention mentioned above. The request name can
|
|
be stored in the form as a hidden parameter. The form action must specify
|
|
the Run SQL Request (iWADbExec) URI. When the form is submitted, the URL
|
|
is built with the values specified, iSeries Access for Web is invoked, and
|
|
the request is executed. Sample HTML source is shown below: </p>
|
|
<pre><HTML>
|
|
<BODY>
|
|
<FORM action="http://server:port/webaccess/iWADbExec" method="get">
|
|
Enter a customer name and press <B>OK</b> to retrieve account information.<br>
|
|
<input type="text" name="iwaparm_1" value=""/>
|
|
<input type="submit" name="ok" value=" OK "/>
|
|
<input type="hidden" name="request" value="promptedRequest"/>
|
|
</FORM>
|
|
</BODY>
|
|
</HTML></pre>
|
|
</dd>
|
|
<dt class="dlterm" id="rzammdatabaseconsiderations__copydatatotable"><a name="rzammdatabaseconsiderations__copydatatotable"><!-- --></a>Copy data to table</dt>
|
|
<dd>If you are copying data to a table, and the chosen file type is Extensible
|
|
Markup Language (XML), the file to copy must be in a concise format. This
|
|
format might or might not contain an embedded schema element and its supporting
|
|
elements. In its simplest form, the XML document must be structured as shown
|
|
below.<pre><?xml version="1.0" encoding="utf-8" ?>
|
|
<QiwaResultSet version="1.0">
|
|
<RowSet>
|
|
<Row number="1">
|
|
<Column1 name="FNAME">Jane</Column1>
|
|
<Column2 name="BALANCE">100.25</Column2>
|
|
</Row>
|
|
<Row number="2">
|
|
<Column1 name="FNAME">John</Column1>
|
|
<Column2 name="BALANCE">200.00</Column2>
|
|
</Row>
|
|
</RowSet>
|
|
</QiwaResultSet></pre>
|
|
<p>This format consists of the XML directive,
|
|
followed by the root element QiwaResultSet. If the encoding attribute is
|
|
not specified in the XML directive, copy data to table will assume the document
|
|
is encoded in utf-8. The root element contains a version attribute. The
|
|
version corresponding to this format of XML is 1.0. The RowSet element is
|
|
a container for all the rows of data that follow. These rows of data are
|
|
contained within Row elements. Each Row element must have a unique numeric
|
|
number attribute. Within each Row element is one or more Column elements.
|
|
Each column element within a row must be unique. This is accomplished by
|
|
adding a sequential numeric suffix. For example, Column1, Column2, Column3,
|
|
Columnx, where 'x' is the number of columns in the row. Each column must
|
|
also have a name attribute. The name corresponds to the column name in the
|
|
relational table on the server. If this simple format is used, the 'Validate
|
|
document with its schema' setting must be turned off since the document does
|
|
not contain a schema.</p>
|
|
<p>Although not required, an XML Schema may also
|
|
be included in the document. Examine an XML document generated by Run SQL
|
|
to get an idea of how a schema is structured. Also, visit the <a href="http://www.w3.org" target="_blank">World Wide Web Consortium's Web site</a> for additional
|
|
information about XML schemas.</p>
|
|
</dd>
|
|
<dt class="dlterm" id="rzammdatabaseconsiderations__importquery"><a name="rzammdatabaseconsiderations__importquery"><!-- --></a><img src="./delta.gif" alt="Start of change" />Import Query<img src="./deltaend.gif" alt="End of change" /></dt>
|
|
<dd><img src="./delta.gif" alt="Start of change" />Query files can contain much more information in them than just the SQL
|
|
statement. Since Run SQL function stores only the SQL statement, some considerations
|
|
need to be made to get identical query results. In cases where the Query file
|
|
contains special instructions for the query, you might need to use the Customize
|
|
function to create a new iSeries Access for Web database connection to honor
|
|
the special case. Default iSeries Access for Web database connections will
|
|
run database query requests using SQL naming conventions and using the locale
|
|
specific defaults for the current user profile. Known special cases requiring
|
|
the creation of non-default database connections, with the special attributes
|
|
set, are: <ul><li> System naming convention is used for the table designation </li>
|
|
<li>Date and time formats other than locale defaults are specified in the
|
|
query </li>
|
|
<li>Decimal separator character other than the locale default is used in the
|
|
query </li>
|
|
<li>A non-default sort sequence is specified</li>
|
|
</ul>
|
|
iSeries Access for Web cannot determine the CCSID of the contents of
|
|
the query file. If the user profile that is importing the query has a CCSID
|
|
that does not match the CCSID of the data in the query file, incorrect conversions
|
|
or conversion errors might result. Users can set a CCSID value during the
|
|
import of the query to overcome this problem.<img src="./deltaend.gif" alt="End of change" /></dd>
|
|
</dl>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzammhdatabase.htm" title="Access database tables, run SQL statements, extract system object information into a database table, import Client Access Data Transfer requests, and import Query for iSeries and DB2 UDB for iSeries Query Manager query files.">Database</a></div>
|
|
</div>
|
|
<div class="relref"><strong>Related reference</strong><br />
|
|
<div><a href="rzammhcustomize.htm" title="iSeries Access for Web provides support to tailor the product.">Customize</a></div>
|
|
</div>
|
|
<div class="relinfo"><strong>Related information</strong><br />
|
|
<div><a href="../rzahh/jdbcproperties.htm">IBM Toolbox for Java JDBC properties</a></div>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html> |