http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/command_interface/src/asciidoc/_chapters/perlpython.adoc ---------------------------------------------------------------------- diff --git a/docs/command_interface/src/asciidoc/_chapters/perlpython.adoc b/docs/command_interface/src/asciidoc/_chapters/perlpython.adoc index f560abd..2516849 100644 --- a/docs/command_interface/src/asciidoc/_chapters/perlpython.adoc +++ b/docs/command_interface/src/asciidoc/_chapters/perlpython.adoc @@ -23,17 +23,17 @@ */ //// -= Run TrafCI From Perl or Python += Run trafci From Perl or Python -You can execute SQL statements in Perl or Python by invoking the TrafCI +You can execute SQL statements in Perl or Python by invoking the trafci Perl or Python wrapper script. -These instructions assume that you installed the TrafCI product. For more information, +These instructions assume that you installed the trafci product. For more information, see <<install, Install and Configure>>. == Set the Login Environment Variables -Before launching TrafCI from Perl or Python, set these login environment variables: +Before launching trafci from Perl or Python, set these login environment variables: [cols="45%l,55%",options="header"] @@ -181,9 +181,9 @@ script is `trafci.py`. By default, these wrapper scripts are located in the `bin | Linux/Unix | <trafci-installation-directory>/trafci/bin |=== -_trafci-installation-directory_ is the directory where you installed the TrafCI software files. +_trafci-installation-directory_ is the directory where you installed the trafci software files. -== Launch TrafCI From the Perl or Python Command Line +== Launch trafci From the Perl or Python Command Line You can launch the Perl or Python wrapper scripts as shown below: @@ -197,62 +197,26 @@ You can launch the Perl or Python wrapper scripts as shown below: <<< === Example Perl Program (`sample.pl`) -[source,perl] ----- -use lib 'C:\\Program Files (x86)\\Apache Software Foundation\\Trafodion Command Interface\\lib\\perl'; -use Session; - -# create a new session -$sess = Session->new(); - -# connect to the database -$sess->connect("user1","password","16.123.456.78","37800"); - -$retval=$sess->execute(" set schema TRAFODION.CI_SAMPLE "); -print $retval; +You can download the `sample.pl` example from +http://trafodion.incubator.apache.org/docs/command_interface/resources/source/sample.pl. -# Execute sample queries -$retval=$sess->execute("select * from employee"); print $retval; -$retval=$sess->execute("get statistics"); print $retval; +Alternatively, copy and paste the following code into a file named `sample.pl`: -# disconnect from the database -print "\n\nSession 1: Disconnecting first session. \n\n"; -$sess->disconnect(); +[source,perl] +---- +include::{sourcedir}/sample.pl[] ---- <<< === Example Python Program (`sample.py`) -[source,python] ----- -import os import sys +You can download the `sample.py` example from +http://trafodion.incubator.apache.org/docs/command_interface/resources/source/sample.py. -## Modify this path -sys.path.append("C:\\Program Files (x86)\\Apache Software Foundation\\Trafodion Command Interface\\lib\\python") -import Session +Alternatively, copy and paste the following code into a file named `sample.py`: -# create a new session -sess = Session.Session() - -# Connect to the database -x=sess. connect ("user1","password","16.123.456.78","37800") - -# Execute sample queries - -# execute takes the query string as argument -setSchema = "set schema TRAFODION.CI_SAMPLE" -selectTable = "select * from employee" -getStats = "get statistics" - -#Contruct a list of SQL statements to be executed -queryList = [setSchema, selectTable, getStats] print "\n"; - -for query in queryList: - print sess.execute (query) - -# disconnect the session -sess.disconnect() -del sess -sess=None +[source,python] +---- +include::{sourcedir}/sample.py[] ----
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/command_interface/src/asciidoc/_chapters/scripts.adoc ---------------------------------------------------------------------- diff --git a/docs/command_interface/src/asciidoc/_chapters/scripts.adoc b/docs/command_interface/src/asciidoc/_chapters/scripts.adoc index 9c70f7e..e74107d 100644 --- a/docs/command_interface/src/asciidoc/_chapters/scripts.adoc +++ b/docs/command_interface/src/asciidoc/_chapters/scripts.adoc @@ -28,7 +28,7 @@ [[script_create]] == Create a Script File -A script file that you run in TrafCI must be an ASCII text file that contains only these elements: +A script file that you run in trafci must be an ASCII text file that contains only these elements: * <<script_sql_statements, SQL Statements>> * <<script_commands, Commands>> @@ -37,13 +37,13 @@ A script file that you run in TrafCI must be an ASCII text file that contains on For an example, see <script_example, Example Script File>>. -NOTE: You cannot use shell commands in a script file that you run in TrafCI. To create shell scripts -that run TrafCI, see <<perl_or_python, Run TrafCI from Perl or Python>>. +NOTE: You cannot use shell commands in a script file that you run in trafci. To create shell scripts +that run trafci, see <<perl_or_python, Run trafci from Perl or Python>>. [[script_sql_statements]] == SQL Statements -Script files support any of the various SQL statements that you can run in TrafCI. For more information about +Script files support any of the various SQL statements that you can run in trafci. For more information about SQL statements, see the http://trafodion.incubator.apache.org/docs/sql_reference/index.html[_Trafodion SQL Reference Manual_]. @@ -78,7 +78,7 @@ To create sections of commands within a script file, put a section header at the ==== The `_section-name_` cannot begin with a number or an underscore. Each section name in a script file should be unique -because TrafCI executes the first section that it finds that matches the section name in the `@` or `OBEY` command. +because trafci executes the first section that it finds that matches the section name in the `@` or `OBEY` command. For more information, see the <<cmd_at_sign, @ Command>> <<cmd_obey, OBEY Command>>. [[script_example]] @@ -90,8 +90,8 @@ image:{images}/script.jpg[Sample script] == Run a Script File -To run a script file in TrafCI, use the `@` or OBEY command. The `@` and `OBEY` commands run one script file at a time -in TrafCI. To run a script file when launching TrafCI, see <<trafci_run_script, Run Script When Launching TrafCI>>. +To run a script file in trafci, use the `@` or OBEY command. The `@` and `OBEY` commands run one script file at a time +in trafci. To run a script file when launching trafci, see <<trafci_run_script, Run Script When Launching trafci>>. *Example* @@ -146,16 +146,16 @@ the <<cmd_obey, OBEY Command>>. == Log Output -To log output of an TrafCI session while running one script file at a time, use the `SPOOL` or `LOG` command. -When you run an `OBEY` or `@` command, TrafCI displays each command in the script file, the output for each -command, and diagnostic messages in TrafCI. The `SPOOL` or `LOG` command captures this output as it appears -in TrafCI and logs it in a log file. +To log output of an trafci session while running one script file at a time, use the `SPOOL` or `LOG` command. +When you run an `OBEY` or `@` command, trafci displays each command in the script file, the output for each +command, and diagnostic messages in trafci. The `SPOOL` or `LOG` command captures this output as it appears +in trafci and logs it in a log file. For more information, <<interactive_log_output, Log Output>>. == Run Scripts in Parallel -In TrafCI, the `@` and `OBEY` commands allow you to run only one script file at a time. However, the `PRUN` command +In trafci, the `@` and `OBEY` commands allow you to run only one script file at a time. However, the `PRUN` command allows you to run multiple script files simultaneously. The `PRUN` command is most useful for running sets of data definition language (DDL) statements simultaneously, which http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/command_interface/src/asciidoc/index.adoc ---------------------------------------------------------------------- diff --git a/docs/command_interface/src/asciidoc/index.adoc b/docs/command_interface/src/asciidoc/index.adoc index c39e642..a9d1bec 100644 --- a/docs/command_interface/src/asciidoc/index.adoc +++ b/docs/command_interface/src/asciidoc/index.adoc @@ -35,6 +35,7 @@ :title-logo-image: ../../../shared/trafodion-logo.jpg :images: ../images +:sourcedir: ../../resources/source :leveloffset: 1 // The directory is called _chapters because asciidoctor skips direct @@ -56,9 +57,12 @@ specific language governing permissions and limitations under the License. Microsoft® and Windows® are U.S. registered trademarks of Microsoft Corporation. Java® is a registered trademark of Oracle and/or its affiliates. DbVisualizer⢠is a trademark of DbVis Software AB. +*Revision History* + [cols="2",options="header"] |=== | Version | Date +| 2.0.0 | To be announced. | 1.3.0 | January, 2016 |=== http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/command_interface/src/resources/source/sample.pl ---------------------------------------------------------------------- diff --git a/docs/command_interface/src/resources/source/sample.pl b/docs/command_interface/src/resources/source/sample.pl new file mode 100644 index 0000000..8fdfd38 --- /dev/null +++ b/docs/command_interface/src/resources/source/sample.pl @@ -0,0 +1,19 @@ +use lib 'C:\\Program Files (x86)\\Apache Software Foundation\\Trafodion Command Interface\\lib\\perl'; +use Session; + +# create a new session +$sess = Session->new(); + +# connect to the database +$sess->connect("user1","password","16.123.456.78","23400"); + +$retval=$sess->execute(" set schema TRAFODION.CI_SAMPLE "); +print $retval; + +# Execute sample queries +$retval=$sess->execute("select * from employee"); print $retval; +$retval=$sess->execute("get statistics"); print $retval; + +# disconnect from the database +print "\n\nSession 1: Disconnecting first session. \n\n"; +$sess->disconnect(); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/command_interface/src/resources/source/sample.py ---------------------------------------------------------------------- diff --git a/docs/command_interface/src/resources/source/sample.py b/docs/command_interface/src/resources/source/sample.py new file mode 100644 index 0000000..a41283e --- /dev/null +++ b/docs/command_interface/src/resources/source/sample.py @@ -0,0 +1,29 @@ +import os import sys + +## Modify this path +sys.path.append("C:\\Program Files (x86)\\Apache Software Foundation\\Trafodion Command Interface\\lib\\python") +import Session + +# create a new session +sess = Session.Session() + +# Connect to the database +x=sess. connect ("user1","password","16.123.456.78","23400") + +# Execute sample queries + +# execute takes the query string as argument +setSchema = "set schema TRAFODION.CI_SAMPLE" +selectTable = "select * from employee" +getStats = "get statistics" + +#Contruct a list of SQL statements to be executed +queryList = [setSchema, selectTable, getStats] print "\n"; + +for query in queryList: + print sess.execute (query) + +# disconnect the session +sess.disconnect() +del sess +sess=None http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/pom.xml ---------------------------------------------------------------------- diff --git a/docs/spj_guide/pom.xml b/docs/spj_guide/pom.xml new file mode 100644 index 0000000..d569367 --- /dev/null +++ b/docs/spj_guide/pom.xml @@ -0,0 +1,289 @@ +<?xml version="1.0" encoding="UTF-8"?> +<project xmlns="http://maven.apache.org/POM/4.0.0"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd";> + <!-- +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ +--> + <modelVersion>4.0.0</modelVersion> + <groupId>org.apache.trafodion</groupId> + <artifactId>spj-guide</artifactId> + <version>${env.TRAFODION_VER}</version> + <packaging>pom</packaging> + <name>Trafodion SPJ Guide</name> + <description>This guide describes how to develop, deploy, and manage Stored Procedures in Java (SPJs) on the Trafodion database.</description> + <url>http://trafodion.incubator.apache.org</url> + <inceptionYear>2015</inceptionYear> + + <parent> + <groupId>org.apache.trafodion</groupId> + <artifactId>trafodion</artifactId> + <version>1.3.0</version> + <relativePath>../../pom.xml</relativePath> + </parent> + + + <licenses> + <license> + <name>The Apache Software License, Version 2.0</name> + <url>http://www.apache.org/licenses/LICENSE-2.0.txt</url> + <distribution>repo</distribution> + <comments>A business-friendly OSS license</comments> + </license> + </licenses> + + <organization> + <name>Apache Software Foundation</name> + <url>http://www.apache.org</url> + </organization> + + <issueManagement> + <system>JIRA</system> + <url>http://issues.apache.org/jira/browse/TRAFODION</url> + </issueManagement> + + <scm> + <connection>scm:git:http://git-wip-us.apache.org/repos/asf/incubator-trafodion.git</connection> + <developerConnection>scm:git:https://git-wip-us.apache.org/repos/asf/incubator-trafodion.git</developerConnection> + <url>https://git-wip-us.apache.org/repos/asf?p=incubator-trafodion.git</url> + <tag>HEAD</tag> + </scm> + + <ciManagement> + <system>Jenkins</system> + <url>https://jenkins.esgyn.com</url> + </ciManagement> + + <properties> + <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> + <asciidoctor.maven.plugin.version>1.5.2.1</asciidoctor.maven.plugin.version> + <asciidoctorj.pdf.version>1.5.0-alpha.11</asciidoctorj.pdf.version> + <asciidoctorj.version>1.5.4</asciidoctorj.version> + <rubygems.prawn.version>2.0.2</rubygems.prawn.version> + <jruby.version>9.0.4.0</jruby.version> + </properties> + + <repositories> + <repository> + <id>rubygems-proxy-releases</id> + <name>RubyGems.org Proxy (Releases)</name> + <url>http://rubygems-proxy.torquebox.org/releases</url> + <releases> + <enabled>true</enabled> + </releases> + <snapshots> + <enabled>false</enabled> + </snapshots> + </repository> + </repositories> + + <dependencies> + <dependency> + <groupId>rubygems</groupId> + <artifactId>prawn</artifactId> + <version>${rubygems.prawn.version}</version> + <type>gem</type> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>org.jruby</groupId> + <artifactId>jruby-complete</artifactId> + <version>${jruby.version}</version> + </dependency> + <dependency> + <groupId>org.asciidoctor</groupId> + <artifactId>asciidoctorj</artifactId> + <version>${asciidoctorj.version}</version> + </dependency> + </dependencies> + + <build> + <plugins> + <plugin> + <groupId>de.saumya.mojo</groupId> + <artifactId>gem-maven-plugin</artifactId> + <version>1.0.10</version> + <configuration> + <!-- align JRuby version with AsciidoctorJ to avoid redundant downloading --> + <jrubyVersion>${jruby.version}</jrubyVersion> + <gemHome>${project.build.directory}/gems</gemHome> + <gemPath>${project.build.directory}/gems</gemPath> + </configuration> + <executions> + <execution> + <goals> + <goal>initialize</goal> + </goals> + </execution> + </executions> + </plugin> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-resources-plugin</artifactId> + <version>2.7</version> + <configuration> + <encoding>UTF-8</encoding> + <attributes> + <generateReports>false</generateReports> + </attributes> + </configuration> + </plugin> + <plugin> + <groupId>org.asciidoctor</groupId> + <artifactId>asciidoctor-maven-plugin</artifactId> + <version>${asciidoctor.maven.plugin.version}</version> + <dependencies> + <dependency> + <groupId>org.asciidoctor</groupId> + <artifactId>asciidoctorj-pdf</artifactId> + <version>${asciidoctorj.pdf.version}</version> + </dependency> + <dependency> + <groupId>org.asciidoctor</groupId> + <artifactId>asciidoctorj</artifactId> + <version>${asciidoctorj.version}</version> + </dependency> + </dependencies> + <configuration> + <sourceDirectory>${basedir}/src</sourceDirectory> + </configuration> + <executions> + <execution> + <id>generate-html-doc</id> + <goals> + <goal>process-asciidoc</goal> + </goals> + <phase>site</phase> + <configuration> + <doctype>book</doctype> + <backend>html5</backend> + <sourceHighlighter>coderay</sourceHighlighter> + <outputDirectory>${basedir}/target/site</outputDirectory> + <requires> + <require>${basedir}/../shared/google-analytics-postprocessor.rb</require> + </requires> + <attributes> + <!-- Location of centralized stylesheet --> + <stylesheet>${basedir}/../shared/trafodion-manuals.css</stylesheet> + <project-version>${env.TRAFODION_VER}</project-version> + <build-date>${maven.build.timestamp}</build-date> + <google-analytics-account>UA-72491210-1</google-analytics-account> + </attributes> + </configuration> + </execution> + <execution> + <id>generate-pdf-doc</id> + <phase>site</phase> + <goals> + <goal>process-asciidoc</goal> + </goals> + <configuration> + <doctype>book</doctype> + <backend>pdf</backend> + <sourceHighlighter>coderay</sourceHighlighter> + <outputDirectory>${basedir}/target</outputDirectory> + <attributes> + <pdf-stylesdir>${basedir}/../shared</pdf-stylesdir> + <pdf-style>trafodion</pdf-style> + <icons>font</icons> + <pagenums/> + <toc/> + <idprefix/> + <idseparator>-</idseparator> + <project-version>${env.TRAFODION_VER}</project-version> + <build-date>${maven.build.timestamp}</build-date> + </attributes> + </configuration> + </execution> + </executions> + </plugin> + <!-- Rename target/site/index.pdf to client-install-guide.pdf --> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-antrun-plugin</artifactId> + <version>1.8</version> + <inherited>false</inherited> + <executions> + <execution> + <id>populate-release-directories</id> + <phase>post-site</phase> + <configuration> + <target name="Populate Release Directories"> + <!-- The website uses the following organization for the docs/target/docs directory: + - To ensure a known location, the base directory contains the LATEST version of the web book and the PDF files. + - The know location is docs/target/docs/<document> + - target/docs/<version>/<document> contains version-specific renderings of the documents. + - target/docs/<version>/<document> contains the PDF version and the web book. The web book is named index.html + --> + <!-- Copy the PDF file to its target directories --> + <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/spj_guide/Trafodion_SPJ_Guide.pdf" /> + <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/${project.version}/spj_guide/Trafodion_SPJ_Guide.pdf" /> + <!-- Copy the Web Book files to their target directories --> + <copy todir="${basedir}/../target/docs/spj_guide"> + <fileset dir="${basedir}/target/site"> + <include name="**/*.*"/> <!--All sub-directories, too--> + </fileset> + </copy> + <copy todir="${basedir}/../target/docs/${project.version}/spj_guide"> + <fileset dir="${basedir}/target/site"> + <include name="**/*.*"/> <!--All sub-directories, too--> + </fileset> + </copy> + </target> + </configuration> + <goals> + <goal>run</goal> + </goals> + </execution> + </executions> + </plugin> + </plugins> + </build> + + <!-- Included because this is required. No reports are generated. --> + <reporting> + <excludeDefaults>true</excludeDefaults> + <plugins> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-project-info-reports-plugin</artifactId> + <version>2.8</version> + <reportSets> + <reportSet> + <reports> + </reports> + </reportSet> + </reportSets> + </plugin> + </plugins> + </reporting> + + <distributionManagement> + <site> + <id>trafodion.incubator.apache.org</id> + <name>Trafodion Website at incubator.apache.org</name> + <!-- On why this is the tmp dir and not trafodion.incubator.apache.org, see + https://issues.apache.org/jira/browse/HBASE-7593?focusedCommentId=13555866&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-13555866 + --> + <url>file:///tmp</url> + </site> + </distributionManagement> +</project> http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/about.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/about.adoc b/docs/spj_guide/src/asciidoc/_chapters/about.adoc new file mode 100644 index 0000000..c4b69cd --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/about.adoc @@ -0,0 +1,200 @@ +//// +/** +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ + */ +//// + += About This Document +This guide describes how to develop, deploy, and manage Stored Procedures in Java (SPJs) on the Trafodion database. + +== Intended Audience +This manual is intended for application programmers who are writing and compiling Java code for stored procedures +and for database administrators who are deploying and managing Stored Procedures in Java (SPJs) on the Trafodion +database. The reader should know: + +* The Java programming language. +* JDBC and the Trafodion JDBC Type-4 Driver. +* Structured Query Language (SQL) and database terms and concepts. + +Although not required, it helps to be familiar with the part of the ANSI SQL/Foundation standard called +SQL/JRT (Java Routines and Types) on which this implementation of stored procedures is based. + +== Document Organization + +This document is organized as follows: + +[cols="30%,70%", options="header"] +|=== +| Chapter | Description +| *<<introduction, Introduction>>* | Defines what an SPJ is, describes the benefits of using SPJs on the database, +and lists steps for developing and deploying SPJs on Trafodion. +| *<<get-started, Get Started>>* | Describes the software requirements for using SPJs on the Trafodion database. +| *<<develop-spj-methods, Develop SPJ Methods>>* | Provides guidelines for writing and compiling a Java method to be used as the +body of a stored procedure. +| *<<xeploy-spj-jar-files, Deploy SPJ JAR Files>>* | Explains how to deploy SPJ JAR files on the Trafodion database. +| *<<create-spjs, Create SPJs>>* | Explains how to create, drop, and alter an SPJ on the Trafodion database. +| *<<grant-privileges, Grant Privileges>>* | Explains how to grant and revoke privileges for executing SPJs on the +Trafodion database. +| *<<execute-spjs, Execute SPJs>>* | Explains how to execute an SPJ on the Trafodion database by using the CALL statement. +| *<<performance-and-troubleshooting, Performance and Troubleshooting>>* | +Describes how to improve and monitor the performance of SPJs and provides guidelines for troubleshooting common problems. +| *<<sample-spjs, Appendix A: Sample SPJs>>* | Provides examples of SPJ methods that demonstrate business logic on the Trafodion database. +| *<<sample-database, Appendix B: Sample Database>>* | Describes the sample database on which the Sample SPJs operate. +|=== + +== New and Changed Information +This is a new manual. + +== Notation Conventions +This list summarizes the notation conventions for syntax presentation in this manual. + +* UPPERCASE LETTERS ++ +Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. ++ +``` +SELECT +``` + +* lowercase letters ++ +Lowercase letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. ++ +``` +file-name +``` + +* [ ] Brackets ++ +Brackets enclose optional syntax items. ++ +``` +DATETIME [start-field TO] end-field +``` ++ +A group of items enclosed in brackets is a list from which you can choose one item or none. ++ +The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. ++ +For example: ++ +``` +DROP SCHEMA schema [CASCADE] +DROP SCHEMA schema [ CASCADE | RESTRICT ] +``` + +* { } Braces ++ +Braces enclose required syntax items. ++ +``` +FROM { grantee [, grantee ] ... } +``` ++ +A group of items enclosed in braces is a list from which you are required to choose one item. ++ +The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. ++ +For example: ++ +``` +INTERVAL { start-field TO end-field } +{ single-field } +INTERVAL { start-field TO end-field | single-field } +``` +* | Vertical Line ++ +A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. +``` +{expression | NULL} +``` + +* … Ellipsis ++ +An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. ++ +``` +ATTRIBUTE[S] attribute [, attribute] ... +{, sql-expression } ... +``` ++ +An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. ++ +For example: ++ +``` +expression-n ... +``` + +<<< +* Punctuation ++ +Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. ++ +``` +DAY (datetime-expression) +@script-file +``` ++ +Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. ++ +For example: ++ +``` +"{" module-name [, module-name] ... "}" +``` + +* Item Spacing ++ +Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. ++ +``` +DAY (datetime-expression) DAY(datetime-expression) +``` ++ +If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: ++ +``` +myfile.sh +``` + +* Line Spacing ++ +If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. ++ +This spacing distinguishes items in a continuation line from items in a vertical list of selections. ++ +``` +match-value [NOT] LIKE _pattern + [ESCAPE esc-char-expression] +``` + +<<< +== Comments Encouraged +The Trafodion community encourages your comments concerning this document. We are committed to providing documentation that meets your +needs. Send any errors found, suggestions for improvement, or compliments to: + [email protected] + +Include the document title and any comment, error found, or suggestion for improvement you have concerning this document. +Or, even better, join our community and help us improve our documentation. Please refer to +http://trafodion.incubator.apache.org/contributing_redirect.html[Trafodion Contributor Guide] for details. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/create_spjs.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/create_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/create_spjs.adoc new file mode 100644 index 0000000..11c2bc1 --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/create_spjs.adoc @@ -0,0 +1,508 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[create-spjs]] += Create SPJs + +After creating libraries for the SPJ JAR files on the Trafodion platform, +you can create the procedures in the Trafodion database. To create or +drop a procedure from the Trafodion database, use the HP Database Manager +(HPDM). + +Follow these procedures: + +* link:#_bookmark116["Create a Procedure" (page 37)] +* link:#_bookmark128["Drop a Procedure" (page 42)] +* link:#bookmark130["Display Procedures and Their Properties" (page 42)] +This chapter also covers: +* link:#_bookmark114["Required Privileges for Creating or Dropping an +SPJ" (page 37)] +* link:#_bookmark136["Altering an SPJ and Its Java Bytecode" (page 44)] + +== Required Privileges for Creating or Dropping an SPJ + +To create an SPJ in a schema, you must be the schema owner, or you must +have the CREATE_PROCEDURE privilege for that schema, and you must have +the CREATE_PROCEDURE privilege for the SQL_OPERATIONS component. You +must also have the USAGE privilege for the library that will be used in +the creation of the stored procedure. For more information, see the +requirements in link:#_bookmark116["Create a Procedure" (page 37)]. + +To drop an SPJ from a schema, you must be the schema owner, or you must +have the DROP_PROCEDURE privilege for that schema. For more information, +see the requirements in link:#_bookmark128["Drop] link:#_bookmark128[a +Procedure" (page 42)]. + +To display the current ownership and privileges, see +link:#bookmark130["Display Procedures and Their Properties"] +link:#bookmark130[(page 42)]. + +== Create a Procedure + +The Create Procedure tool registers an existing Java method as a stored +procedure in Java (SPJ) within SQL. + +NOTE: HPDM creates procedures with default privileges only. For more +information about privileges for SPJs, see the Grant/Revoke Privileges +Tool in HPDM or the GRANT PROCEDURE statement in the __Trafodion SQL +Reference Manual__. + +Requirements: + +* You must have the CREATE_PROCEDURE privilege for the SQL_OPERATIONS +component. For more information, see the component privileges in the _HP +Database Manager (HPDM) User Guide_ or the online help in HPDM. +* You must have been granted the USAGE privilege for the library that +will be used in the creation of the stored procedure. This privilege +provides you with read access to the library's underlying JAR file, +which contains the SPJ Java method. For more information, see the +Grant/Revoke Privileges Tool in HPDM or the GRANT LIBRARY statement in +the __Trafodion SQL Reference Manual__. +* You must either be the owner of the schema or have the +CREATE_PROCEDURE privilege for the schema to create the procedure. The +schema owner can grant such a schema-level privilege to other users or +roles. For more information, see the Grant/Revoke Privileges Tool in +HPDM or the GRANT SCHEMA statement in the _Trafodion SQL Reference +Manual_ + +To create a procedure: + +1. In HPDM, select the Database area. +2. Expand the navigation tree pane so that you can see the name of the +schema in which you want to create the procedure and the Procedures +folder under the schema. +3. Right-click the Procedures folder, and select Create Procedure. The +Create Procedure dialog box appears. +4. In the Procedure Name field, enter a name for the stored procedure. +For detailed information about the Procedure Name field, see +link:#_bookmark120["Use the Create Procedure Dialog Box" (page 39)]. +5. In the Code group box, click [ Browse ] to find a Java method. + +The [ Browse ] button opens Library Browser. Use the browser to select a +method from a JAR file within a library. See link:#_bookmark109["Use the +Library Browser" (page 35)]. If a library containing the JAR file does +not exist on the Trafodion platform, you can create one by clicking the [ +Create ] button in the Library Browser. See link:#_bookmark98["Use the +Create Library Dialog Box" (page 31)]. + +1. In the Parameters group box, verify that the SQL parameters are +mapped correctly to the Java parameters of the SPJ method. + +NOTE: The result set parameters (java.sql.ResultSet[]) in the Java +signature do not have corresponding SQL parameters. + +a. To change an SQL parameter, such as the name of the parameter, the +SQL data type, +or the parameter mode or direction (IN, INOUT, or OUT), select the +parameter and click + +[ Edit ]. The Edit Parameter dialog box appears. + +a. In the Edit Parameter dialog box, enter a new name for the +parameter, select a different SQL data type, if permitted, or select a +different parameter mode (direction), if permitted. + +When changing the parameter name, note that the parameter name: + +* Is not case-sensitive +* Must not be a reserved word +* Must not contain spaces +* Must begin with a letter, digit, or underscore + +When changing the SQL data type, select a data type that is appropriate +for the parameter of the underlying Java method. For information about +SQL data types, see the __Trafodion SQL Reference Manual__. + +For character string parameters, select either ISO88591 or UCS2 +depending on the column definition in the database. For guidelines, see +the __Trafodion Character Sets Guide__. + +a. Click [ OK ] to accept the changes, or click [ Cancel ] to quit the +Edit Parameter dialog box. +b. Repeat steps a to c for each parameter that you want to change. + +1. In the Attributes group box, if your SPJ method returns result sets, +enter the maximum number of result sets to be returned for Number of +dynamic result sets. The valid range is 1 to 255 inclusive. The actual +number of result sets returned by the SPJ method can be less than or +equal to this number. + +NOTE: The Number of dynamic result sets control is enabled only if the +method signature contains a java.sql.ResultSet[] object. + +1. If your SPJ method performs any SQL operations, select the Accesses +Database option. + +NOTE: If you do not select the Accesses Database option and your SPJ +method performs SQL operations, SQL returns an error when trying to +execute the procedure. + +1. Select either Invoker or Definer for the external security of the +stored procedure. + +* The Invoker setting determines that users can execute, or invoke, the +stored procedure using the privileges of the user who invokes the stored +procedure. This behavior is referred to as __invoker rights__. +* The Definer setting determines that users can execute, or invoke, the +stored procedure using the privileges of the user who created the stored +procedure. This behavior is referred to as __definer rights__. The +advantage of definer rights is that users are allowed to manipulate data +by invoking the stored procedure without having to be granted privileges +to the underlying database objects. + +For more information, see link:#_bookmark124["Understand External +Security" (page 41)]. + +1. For the Transaction Required attribute, select either Yes to require +the procedure to run in a transaction inherited from the calling +application (the default behavior) or No to allow the procedure to run +without an inherited transaction. For more information, see +link:#_bookmark157["Transaction] link:#_bookmark157[Behavior" (page +49)]. +2. Click [ Create ] to create the procedure. + +Related Topics + +link:#_bookmark120["Use the Create Procedure Dialog Box" (page 39)] + +== Use the Create Procedure Dialog Box + +This table shows reference information for the Create Procedure dialog +box: + +[cols=",,",options="header",] +|=== +Group Box +Control or Field +Action +Name +____ + +Catalog +Name of the catalog where the procedure will be created. This is a +read-only field. +Schema +Name of the schema where the procedure will be created. This is a +read-only field. +Procedure Name +Enter a name for the procedure. The name must be unique and must not +exist for any procedure, table, or view in the same schema. The +procedure name is not case-sensitive. The database engine automatically +qualifies the procedure name with the name of the catalog and schema in +which you are creating the procedure. For example, if you enter +*monthlyorders* as the procedure name, the database engine stores the +procedure as + +__catalog-name__.__schema-name__.MONTHLYORDERS. +Code +Library +Click [ Browse ] to navigate to a library or JAR file. +Class Name +Select a class. +Method Name +When you select a class, the method names and parameter types for that +class appear in the right pane. +NOTE: Only methods that can be used in a procedure are visible. For +details, see link:#_bookmark123["Use a Method in a Procedure" (page +40)]. +Parameters +Name +____ + +Is the name for this SQL parameter. +Direction +IN passes data to a procedure +INOUT passes data to and accepts data from a procedure. The parameter +|=== + +must be an array. + + +* OUT accepts data from a procedure. The parameter must be an array. + +SQL Data Type + +Displays the SQL data type that is the best match for the Java signature +in the + +Java Data Type column. + +Java Data Type Displays the signature for the Java method that you +selected. + +[ Edit ] Click to edit the selected parameter. + +Group Box + +Control or Field + +Action + +Attributes + +Number of dynamic result sets + +Accesses Database + +Controls the maximum number of result sets the procedure can return. +This control is enabled only if the method signature contains a +java.sql.ResultSet[] object. If the method contains a result set object, +the valid range is 1 to 255. This value is automatically set to zero if +the selected Java method does not have a java.sql.ResultSet[] object. + +If selected, the procedure performs SQL operations. If cleared, the +procedure does not perform SQL operations. + +NOTE: SQL returns an error when trying to execute the procedure if this +attribute is cleared and the procedure performs SQL operations. + +External Security + +Transaction Required + +Select either Invoker or Definer for the external security of the stored +procedure. + +* The Invoker setting determines that users can execute, or invoke, the +stored procedure using the privileges of the user who invokes the stored +procedure. This behavior is referred to as __invoker rights__. +* The Definer setting determines that users can execute, or invoke, the +stored procedure using the privileges of the user who created the stored +procedure. This behavior is referred to as __definer rights__. The +advantage of definer rights is that users are allowed to manipulate data +by invoking the stored procedure without having to be granted privileges +to the underlying database objects. + +For more information, see link:#_bookmark124["Understand External +Security" (page 41)]. + +Select either Yes to require the procedure to run in a transaction +inherited from the calling application (the default behavior) or No to +allow the procedure to run without an inherited transaction. For more +information, see link:#_bookmark157["Transaction] +link:#_bookmark157[Behavior" (page 49)]. + +Related Topics + +link:#_bookmark116["Create a Procedure" (page 37)] + +== Use a Method in a Procedure + +To be used in a procedure, a Java method must: + +* Be qualified as public static void. +* Have a java.sql.ResultSet[] parameter at the end of the method +signature if the method uses result sets. + +NOTE: There can be more than one java.sql.ResultSet[] parameter, but +they must all be at the end of the method signature. + +* Have these parameter types: + +Parameter Type or Class + +Type + +character java.lang.String + +java.lang.String[] + +numeric java.lang.Integer + +java.lang.Integer[] java.lang.Long java.lang.Long[] java.lang.Float +java.lang.Float[] + +Parameter Type or Class + +Type + +java.lang.Double java.lang.Double[] java.math.BigDecimal +java.math.BigDecimal[] + +date/timestamp java.sql.Date + +java.sql.Date[] java.sql.Time java.sql.Time[] java.sql.Timestamp +java.sql.Timestamp[] + +primitive short + +short[] int int[] long long[] float float[] double double[] + +result sets java.sql.ResultSet[] + +== Understand External Security + +The external security of an SPJ determines the privileges, or rights, +that users have when executing (or calling) the SPJ. An SPJ can be +created with one of these types of external security: invoker or +definer. + +If an SPJ is created with the invoker type of external security, the SPJ +is executed with __invoker rights__. Invoker rights allow a user who has +the execute privilege on the SPJ to call the SPJ using his or her +existing privileges. In this case, the user must be granted privileges +to access the underlying database objects on which the SPJ operates. If +a user tries to call an SPJ that has invoker external security and that +operates on database objects to which the user does not have privileges, +the CALL statement fails with an error message indicating that the user +does not have the appropriate permissions. Note: Granting a user +privileges to the underlying database objects gives the user direct +access to those database objects, which could pose a risk to more +sensitive or critical data to which users should not have access. For +example, an SPJ might operate on a subset of the data in an underlying +database object, but that database object might contain other more +sensitive or critical data to which users should not have access. + +If an SPJ is created with the definer type of external security, the SPJ +is executed with __definer rights__. Definer rights allow a user who has +the execute privilege on an SPJ to call the SPJ using the privileges of +the user who created the SPJ. In this case, the user does not require +privileges to access the underlying database objects on which the SPJ +operates. Instead, the user is allowed to access or manipulate data in +the underlying database objects by invoking the SPJ. That way, users are +restricted from directly accessing or manipulating more sensitive or +critical data in the database. However, be careful about the users to +whom you grant execute privilege on an SPJ with definer external +security because those users will be able to execute the SPJ without +requiring privileges to the underlying database objects. + +To set the external security of an SPJ, see link:#_bookmark116["Create a +Procedure" (page 37)]. + +== Drop a Procedure + +To drop a procedure, you must own the procedure or have the +DROP_PROCEDURE privilege for the schema. The schema owner can grant such +a schema-level privilege to other users or roles. For example, if the +schema owner granted you privileges to drop all objects in the schema, +you could drop procedures. For more information, see the Grant/Revoke +Privileges Tool in HPDM or the GRANT SCHEMA statement in the __Trafodion +SQL Reference Manual__. + +To drop a procedure: + +1. Under the navigation tree pane, click the Database area. +2. Under My Systems, expand the tree for the Trafodion platform +containing the procedure until you can see the schema folder and the +Procedures folder underneath it. +3. Expand the Procedures folder. +4. Right-click the name of the procedure that you want to drop, and +select Drop Procedure. HPDM asks you to confirm the operation. +5. Click [ Yes ] to continue or [ No ] to quit the operation. + +== Display Procedures and Their Properties + +To display the SPJs in a schema, use either HPDM or trafci: + +* link:#_bookmark131["Using HPDM to Display a Procedure in a Schema" +(page 42)] +* link:#_bookmark133["Using trafci to Display Procedures in a Schema" +(page 43)] + +=== Using HPDM to Display a Procedure in a Schema + +To display one of the procedures in a schema: + +1. Start the HP Database Manager and log on using any user name. +2. Click the Database area. +3. In the navigation tree pane, select a Schema, and expand the schema +so that you can see the object folders underneath it. +4. Expand the Procedures tab in the right pane and select a procedure +name, or open the Procedures folder and select a procedure name in the +tree. HPDM displays the properties of the selected procedure in the +right pane. + +image:media/image7.jpeg[image] + +For more information about the procedure properties displayed in HPDM, +see the _HP Database_ __Manager (HPDM) User Guide__ or the online help +in HPDM. + +==== Using trafci to Display Procedures in a Schema + +In the trafci command-line interface, use the SHOW PROCEDURES command to +display the procedures in a schema. For example, this SHOW PROCEDURES +command displays a list of the procedures in the SALES schema: + +``` +SQL>**set schema demo.sales;** + +--- SQL operation complete. SQL>**show procedures** PROCEDURE NAMES + +-------------------------------------------------------------------------------- +DAILYORDERS LOWERPRICE MONTHLYORDERS ORDERSUMMARY PARTDATA TOTAL PRICE + +SQL> +``` + +You can also use a wild-card pattern to search for a particular +procedure. For example, this SHOW PROCEDURES command displays all the +procedures in the SALES schema that have price in their names: + +``` +SQL>**show procedures %price** + +PROCEDURE NAMES + +-------------------------------------------------------------------------------- +LOWERPRICE TOTALPRICE + +SQL> +``` + +For more information about trafci, see the __HP Database Command +Interface (trafci) Guide__. + +==== Altering an SPJ and Its Java Bytecode + +Occasionally, you might need to update an SPJ or its Java bytecode. The +Java bytecode includes the SPJ's class file and any associated class +files that are packaged in the SPJ JAR file. Suppose that you want to +update the Java bytecode of an SPJ without changing the class name, +method name, or Java signature of the SPJ method. In this case, you +would alter the library by selecting the updated JAR file to upload to +the Trafodion platform, replacing the previous JAR file for the library. +For more information, see link:#_bookmark100["Alter a Library" (page +32)]. + +NOTE: You are prevented from uploading a JAR file that is already in use +by another library in the catalog. This restriction prevents you from +accidentally overwriting a JAR file that has the same name. + +If you plan to use a JAR file that contains an SPJ method that has a +different class name, method name, or signature than the original SPJ +method, you must drop the SPJ from the database before altering the +library. After altering the library, re-create the SPJ in the database. +You must also drop and re-create an SPJ to rename the procedure or +change the SQL parameter definitions even if the Java bytecode remains +the same. For more information, see link:#_bookmark116["Create a +Procedure" (page 37)] and link:#_bookmark128["Drop a Procedure" (page +42)]. + +When you update an SPJ or its Java bytecode, try to avoid making those +changes when client applications are actively calling the SPJ. If you +update an SPJ or its Java bytecode when a client application is calling +the SPJ, the CALL statement might return wrong or inconsistent data to +the calling application. Therefore, schedule a time to update the SPJ or +its Java bytecode when client applications are not actively calling the +SPJ. + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/deploy_spjs.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/deploy_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/deploy_spjs.adoc new file mode 100644 index 0000000..67fed93 --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/deploy_spjs.adoc @@ -0,0 +1,557 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[Deploy-spj-jar-files]] += Deploy SPJ JAR Files + +After developing and packaging the SPJ code into JAR files, you must +move the JAR files from the client workstation to the Trafodion database. + +You can upload a SPJ JAR file and create a library by using +the ADD LIBRARY command in the Trafodion Command Interface (trafci). +You use the CREATE LIBRARY, ALTER LIBRARY, and DROP LIBRARY commands. + +Libraries provide greater security for JAR files because libraries are +database objects whose access is controlled using standard SQL security. + +Deploying a JAR file to a Trafodion instance requires creating a library, +and users must have the required privileges for creating libraries in order +to deploy JAR files. + +See these instructions: + +* <<create-a-library, Create a Library>> +* <alter-a-library, Alter a Library>> +* <<drop-a-library, Drop a Library>> +* <<download-a-jar-file-from-a-library, Download a JAR File From a Library>> +* <<display-libraries-and-their-properties, Display Libraries and Their Properties>> + +Before you start, see +<<required-privileges-for-creating-altering-or-dropping-a-library, "Required Privileges for Creating, Altering, or Dropping a Library">>. + + +== Required Privileges for Creating, Altering, or Dropping a Library + +Deploying a JAR file to the Trafodion database requires creating a +library, and you must have the required privileges for creating +libraries in order to deploy a JAR file. To create a library in a +schema, you must have both the CREATE_LIBRARY component privilege and +the CREATE_LIBRARY schema-level privilege for the schema. For more +information, see the requirements in <create-a-library, Create a Library>>. + +To alter a library in a schema, you must have both the UPDATE privilege +for the library and the ALTER_LIBRARY privilege for the schema. For more +information, see the requirements in +<<alter-a-library, Alter a Library>>. + +To drop a library from a schema, you must have the DROP_LIBRARY +privilege for the schema. For more information, see the requirements in +<<drop-a-library, Drop a Library>>. + +To download a JAR file from a library, you must have the CREATE_LIBRARY +component privilege. For more information, see the requirements in +<<download-a-jar-file-from-a-library, Download a JAR File From a Library>>. + +[[create-a-library]] +== Create a Library + +Refer to the +http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual] +for full documentation of the +http://trafodion.apache.org/docs/sql_reference/index.html#create_library_statement[CREATE LIBRARY] +statement. + +When you create a library, HPDM simultaneously uploads an SPJ JAR file +from the client workstation to the Trafodion database and creates a +library for the JAR file in a schema of the database. + +Requirements: + +* You must have the CREATE_LIBRARY privilege for the SQL_OPERATIONS +component. For more information, see the component privileges in the _HP +Database Manager (HPDM) User Guide_ or the online help in HPDM. +* You must either be the owner of the schema where you will create the +library or have the CREATE_LIBRARY privilege for the schema to create +the library. The schema owner can grant such a schema-level privilege to +other users or roles. For more information, see the + +Grant/Revoke Privileges Tool in HPDM or the GRANT SCHEMA statement in +the __Trafodion SQL Reference Manual__. + +* The JAR file that you select for the library must contain valid Java +bytecode and must not be used by another library in the catalog. +* The JAR file that you select for the library must not exceed a size +threshold if one has been set for HPDM's internal WMS service, the data +source, or the system defaults. + +For example, if the CONTROL QUERY DEFAULT (CQD), +POS_ABSOLUTE_MAX_TABLE_SIZE, + +has been set for the HPS_MANAGEABILITY service, it will limit the size +of JAR files that can be uploaded by HPDM. If the JAR file's size +exceeds the threshold, a warning message is displayed, and the file is +not uploaded. By default, this CQD is not set for HPDM's internal WMS +service, the data source, or the system defaults, and HPDM allows you to +upload JAR files of any size. + +To upload a JAR file and create a library for the JAR file in a schema: + +1. In HPDM, select the Database area. +2. In the navigation tree pane, expand the tree to display the folders +of the schema where you want to create the library. +3. Right-click the Libraries folder, and select Create Library. The +Create Library dialog box appears. For more information, see +link:#_bookmark98["Use the Create Library Dialog Box" (page 31)]. +4. In the Create Library dialog box, enter a name for the library. + +The name must be unique among the libraries in the schema and must not +exceed 128 characters. The library name is not case-sensitive. For +example, if you enter Payroll as the library name, the database engine +stores the library as + +__catalog-name__.__schema-name__.PAYROLL. The database engine +automatically qualifies the library name with the name of the catalog +and schema in which you are creating the library. + +1. Click the [ Browse ] button to locate and select a JAR file on the +client workstation. The "Select a code file" dialog box appears. +2. In the "Select a code file" dialog box, locate and select the JAR +file on the client workstation, and click [ Open ] to return to the +Create Library dialog box. The Create Library dialog box displays the +full path of the JAR file in the Code File field. +3. Click [ Create ] to create the library, or click [ Close ] to quit +the dialog box without creating the library. + +If you click [ Create ], HPDM validates the JAR file, uploads the JAR +file, and creates a library for the JAR file in the schema. + +1. When notified that the library was created successfully, click [ OK +]. +2. To upload another JAR file and create a library, repeat steps 4 +through 8. +3. When you are done uploading JAR files and creating libraries, click +the [ Close ] button image:media/image4.png[image]) to quit the Create +Library dialog box and return to the HPDM main window. + +Related Topics + +link:#_bookmark98["Use the Create Library Dialog Box" (page 31)] +link:#_bookmark100["Alter a Library" (page 32)] + +link:#_bookmark105["Drop a Library" (page 33)] + +link:#_bookmark107["Download a JAR File From a Library" (page 34)] +link:#_bookmark110["Display Libraries and Their Properties" (page 36)] + +[[use-the-create-library-dialog-box]] +== Use the Create Library Dialog Box + +The Create Library dialog box allows you to upload an SPJ JAR file from +the client workstation to the Trafodion database and create a library for +the JAR file in a schema of the database. + +Requirements: + +* You must have the CREATE_LIBRARY privilege for the SQL_OPERATIONS +component. For more information, see the component privileges in the _HP +Database Manager (HPDM) User Guide_ or the online help in HPDM. +* You must either be the owner of the schema where you will create the +library or have the CREATE_LIBRARY privilege for the schema to create +the library. The schema owner can grant such a schema-level privilege to +other users or roles. For more information, see the Grant/Revoke +Privileges Tool in HPDM or the GRANT SCHEMA statement in the __Trafodion +SQL Reference Manual__. +* The JAR file that you select for the library must contain valid Java +bytecode and must not be used by another library in the catalog. +* The JAR file that you select for the library must not exceed a size +threshold if one has been set for HPDM's internal WMS service, the data +source, or the system defaults. + +For example, if the CONTROL QUERY DEFAULT (CQD), +POS_ABSOLUTE_MAX_TABLE_SIZE, + +has been set for the HPS_MANAGEABILITY service, it will limit the size +of JAR files that can be uploaded by HPDM. If the JAR file's size +exceeds the threshold, a warning message is displayed, and the file is +not uploaded. By default, this CQD is not set for HPDM's internal WMS +service, the data source, or the system defaults, and HPDM allows you to +upload JAR files of any size. + +[cols=",,",options="header",] +|=== +[[group-box]] +Group Box +Control or Field +Description or Action +Library +Catalog +Name of the catalog where the library will be created. This is a +read-only field. +Schema +Name of the schema where the library will be created. This is a +read-only field. +Library Name +Enter a name for the library. The name must be unique among the +libraries in the schema and must not exceed 128 characters. The library +name is not case-sensitive. For example, if you enter Payroll as the +library name, the database engine stores the library as + +__catalog-name__.__schema-name__.PAYROLL. The database engine +automatically qualifies the library name with the name of the catalog +and schema in which you are creating the library. +Code File +Code File +Click [ Browse ] to launch the "Select a code file" dialog box and +navigate to a JAR file on the client workstation. +[ Create ] +Creates the library. +[ Close ] +Closes the dialog box without creating a library. +[ Help ] +Displays context-sensitive help for this dialog box. +|=== + +[[related-topics]] +Related Topics + +link:#_bookmark95["Create a Library" (page 29)] +link:#_bookmark100["Alter a Library" (page 32)] link:#_bookmark105["Drop +a Library" (page 33)] + +link:#_bookmark107["Download a JAR File From a Library" (page 34)] + +[[alter-a-library]] +== Alter a Library + +When you alter a library, you can change the underlying JAR file of the +library. + +Requirements: + +* You must have been granted the UPDATE privilege for the library. For +more information, see the Grant/Revoke Privileges Tool in HPDM or the +GRANT LIBRARY statement in the __Trafodion SQL Reference Manual__. +* You must either be the owner of the schema where you will alter the +library or have the ALTER_LIBRARY privilege for the schema to alter the +library. The schema owner can grant such a schema-level privilege to +other users or roles. For more information, see the Grant/Revoke +Privileges Tool in HPDM or the GRANT SCHEMA statement in the __Trafodion +SQL Reference Manual__. +* The JAR file that you select for the library must contain valid Java +bytecode and must not be used by another library in the catalog. +* The JAR file that you select for the library must not exceed a size +threshold if one has been set for HPDM's internal WMS service, the data +source, or the system defaults. + +For example, if the CONTROL QUERY DEFAULT (CQD), +POS_ABSOLUTE_MAX_TABLE_SIZE, + +has been set for the HPS_MANAGEABILITY service, it will limit the size +of JAR files that can be uploaded by HPDM. If the JAR file's size +exceeds the threshold, a warning message is displayed, and the file is +not uploaded. By default, this CQD is not set for HPDM's internal WMS +service, the data source, or the system defaults, and HPDM allows you to +upload JAR files of any size. + +To alter a library and change its underlying JAR file: + +1. In HPDM, select the Database area. +2. In the navigation tree pane, expand the tree to display the folders +of the schema that contains the library, and expand the Libraries folder +to display the libraries within it. +3. Right-click the name of the library, and select Alter Library. If +you selected a system library, a dialog box appears warning you that you +cannot alter a system library. If you selected a user library, the Alter +Library dialog box appears. For more information, see +link:#_bookmark103["Use the Alter] link:#_bookmark103[Library Dialog +Box" (page 33)]. +4. In the Alter Library dialog box, click the [ Browse ] button to +locate and select a new JAR file on the client workstation. The "Select +a code file" dialog box appears. +5. In the "Select a code file" dialog box, locate and select the JAR +file on the client workstation, and click [ Open ] to return to the +Alter Library dialog box. The Alter Library dialog box displays the full +path of the new JAR file in the Code File field. +6. Click [ Alter ] to alter the library, or click [ Close ] to quit the +dialog box without altering the library. + +If you click [ Alter ], a dialog box appears warning you that altering a +library might affect stored procedures that use the library and asking +if you wish to continue the alter operation. Click [ Yes ] to proceed, +or click [ No ] to cancel the operation. If you click [ Yes ], HPDM +validates the JAR file, uploads the JAR file, and alters the library in +the schema. + +1. When notified that the library was altered successfully, click [ OK +] to close the Alter Library dialog box and return to the HPDM main +window. + +Related Topics + +link:#_bookmark103["Use the Alter Library Dialog Box" (page 33)] +link:#_bookmark95["Create a Library" (page 29)] + +link:#_bookmark105["Drop a Library" (page 33)] + +link:#_bookmark107["Download a JAR File From a Library" (page 34)] +link:#_bookmark110["Display Libraries and Their Properties" (page 36)] + +[[use-the-alter-library-dialog-box]] +== Use the Alter Library Dialog Box + +The Alter Library dialog box allows you to alter a library and change +its underlying JAR file. + +Requirements: + +* You must have been granted the UPDATE privilege for the library. For +more information, see the Grant/Revoke Privileges Tool in HPDM or the +GRANT LIBRARY statement in the __Trafodion SQL Reference Manual__. +* You must either be the owner of the schema where you will alter the +library or have the ALTER_LIBRARY privilege for the schema to alter the +library. The schema owner can grant such a schema-level privilege to +other users or roles. For more information, see the Grant/Revoke +Privileges Tool in HPDM or the GRANT SCHEMA statement in the __Trafodion +SQL Reference Manual__. +* The JAR file that you select for the library must contain valid Java +bytecode and must not be used by another library in the catalog. +* The JAR file that you select for the library must not exceed a size +threshold if one has been set for HPDM's internal WMS service, the data +source, or the system defaults. + +For example, if the CONTROL QUERY DEFAULT (CQD), +POS_ABSOLUTE_MAX_TABLE_SIZE, + +has been set for the HPS_MANAGEABILITY service, it will limit the size +of JAR files that can be uploaded by HPDM. If the JAR file's size +exceeds the threshold, a warning message is displayed, and the file is +not uploaded. By default, this CQD is not set for HPDM's internal WMS +service, the data source, or the system defaults, and HPDM allows you to +upload JAR files of any size. + + +[cols=",,",options="header",] +|=== +[[group-box]] +Group Box +Control or Field +Description or Action +Library +Catalog +Name of the catalog where the library will be created. This is a +read-only field. +Schema +Name of the schema where the library will be created. This is a +read-only field. +Library Name +Name of the library. This is a read-only field. +Code File +Code File +Click [ Browse ] to launch the "Select a code file" dialog box and +navigate to a new JAR file on the client workstation. +[ Alter ] +Alters the library. +[ Close ] +Closes the dialog box without altering the library. +[ Help ] +Displays context-sensitive help for this dialog box. +|=== + +[[related-topics]] +Related Topics + +link:#_bookmark100["Alter a Library" (page 32)] +link:#_bookmark95["Create a Library" (page 29)] link:#_bookmark105["Drop +a Library" (page 33)] + +link:#_bookmark107["Download a JAR File From a Library" (page 34)] + +[[drop-a-library]] +== Drop a Library + +Dropping a library removes the library from the schema in the database +and removes the library's underlying JAR file from the Trafodion +database. + +Requirements: + +* You must either be the owner of the schema where you will drop the +library or have the DROP_LIBRARY privilege for the schema to drop the +library. The schema owner can grant such a schema-level privilege to +other users or roles. For more information, see the Grant/Revoke +Privileges Tool in HPDM or the GRANT SCHEMA statement in the __Trafodion +SQL Reference Manual__. + +To drop a library from the database: + +1. In HPDM, select the Database area. +2. In the navigation tree pane, expand the tree to display the folders +of the schema that contains the library, and expand the Libraries folder +to display the libraries within it. +3. Right-click the name of the library, and select Drop Library. The +Drop Library dialog box appears. +4. In the Drop Library dialog box appears, select the Dropping all +procedures and routines using this library check box to drop all stored +procedures that are using the library. If you do not select this option +when stored procedures are using the library, the drop operation will +fail with an error message. +5. Click [ Yes ] to drop the library, or click [ No ] to quit the +dialog box without dropping the library. + +Related Topics + +link:#_bookmark95["Create a Library" (page 29)] +link:#_bookmark100["Alter a Library" (page 32)] + +link:#_bookmark107["Download a JAR File From a Library" (page 34)] +link:#_bookmark110["Display Libraries and Their Properties" (page 36)] + +[[download-a-jar-file-from-a-library]] +== Download a JAR File From a Library + +Requirements: + +* You must have the CREATE_LIBRARY privilege for the SQL_OPERATIONS +component. For more information, see the component privileges in the _HP +Database Manager (HPDM) User Guide_ or the online help in HPDM. + +To download a JAR file from a library: + +1. In HPDM, select the Database area. +2. In the navigation tree pane, expand the tree to display the folders +of the schema that contains the library, and expand the Libraries folder +to display the libraries within it. +3. Right-click the name of the library, and select Browse Library. The +Browse Library dialog box appears with the name of the library selected +in the tree pane and displayed in the right pane. For more information, +see link:#_bookmark109["Use the Library Browser" (page 35)]. +4. Click the [ Download ] button. The Browse For Folder dialog box +appears. +5. In the Browse For Folder dialog box, select a target folder on the +client workstation for downloading the JAR file. +6. Click [ OK ] to start downloading the library's underlying JAR file +to the client workstation, or click [ Cancel ] to quit the Browse For +Folder dialog box and return to the Browse Library dialog box. +7. When notified that the download completed successfully, click [ OK ] +to return to the Browse Library dialog box. + +Related Topics + +link:#_bookmark109["Use the Library Browser" (page 35)] +link:#_bookmark95["Create a Library" (page 29)] + +link:#_bookmark100["Alter a Library" (page 32)] link:#_bookmark105["Drop +a Library" (page 33)] + +link:#_bookmark110["Display Libraries and Their Properties" (page 36)] + +[[use-the-library-browser]] +== Use the Library Browser + +Depending on the context in which the Library Browser is launched, you +can use it to view, create, or drop libraries in the database; to +download a JAR file from a library in the database to the client +workstation; or to select a Java method in a library when creating a +stored procedure. The Library Browser consists of these main parts: + +image:media/image5.jpeg[image] + +Use these buttons to perform various library management tasks: + +Button Function + +[ Create ] + +Launches the Create Library dialog box so that you can create a library +in the database. In the navigation tree pane, select a schema name or +the Libraries folder to activate this button. For more information, see +link:#_bookmark98["Use the Create Library Dialog Box" (page 31)]. + +[ Drop ] + +Launches the Drop Library dialog box so that you can delete a library +from the database. In the navigation tree pane, select the name of a +library to activate this button. For more information, see +link:#_bookmark105["Drop a Library" (page 33)]. + +[ Download ] + +[ Refresh ] + +Launches the Browse For Folder dialog box so that you can select a +folder on the client workstation for downloading a library's underlying +JAR file. In the navigation tree pane, select the name of a library to +activate this button. For more information, see +link:#_bookmark107["Download] link:#_bookmark107[a JAR File From a +Library" (page 34)]. + +Refreshes the data grid with the latest metadata. + +[ OK ] + +[ Close ] + +Accepts your selected Java method for the stored procedure that you are +creating, closes the Library Browser, and returns to the Create +Procedure dialog box. This button appears only if you launched the +Library Browser from the Create Procedure tool. It is activated after +you select a Java class name in the navigation tree pane and then select +a Java method name in the data grid. For more information, see +link:#_bookmark116["Create a Procedure" (page 37)] or +link:#_bookmark120["Use] link:#_bookmark120[the Create Procedure Dialog +Box" (page 39)]. + +Closes the Library Browser. + +[ Help ] Displays context-sensitive help for the Library Browser. + +Related Topics + +link:#_bookmark95["Create a Library" (page 29)] + +link:#_bookmark98["Use the Create Library Dialog Box" (page 31)] +link:#_bookmark105["Drop a Library" (page 33)] + +link:#_bookmark107["Download a JAR File From a Library" (page 34)] +link:#_bookmark116["Create a Procedure" (page 37)] + +link:#_bookmark120["Use the Create Procedure Dialog Box" (page 39)] + +[[display-libraries-and-their-properties]] +== Display Libraries and Their Properties + +To display the libraries in a schema: + +1. Start the HP Database Manager and log on using any user name. +2. Click the Database area. +3. In the navigation tree pane, select a Schema, and expand the schema +so that you can see the object folders underneath it. +4. Expand the Libraries tab in the right pane and select a library +name, or open the Libraries +folder and select a library name in the tree. HPDM displays the +libraries for this schema. + +image:media/image6.jpeg[image] + +For more information about the library properties displayed in HPDM, see +the _HP Database Manager (HPDM) User Guide_ or the online help in HPDM. +
