https://wiki.midrange.com/api.php?action=feedcontributions&user=Zippy1981&feedformat=atomMidrangeWiki - User contributions [en]2024-03-29T11:58:05ZUser contributionsMediaWiki 1.30.0https://wiki.midrange.com/index.php?title=Stored_procedure&diff=12007Stored procedure2016-01-30T04:59:03Z<p>Zippy1981: Mentioned all programs are stored procedures.</p>
<hr />
<div>Stored procedures are a way to extend database functionality. [http://en.wikipedia.org/wiki/Stored_procedure Wikipedia] calls a stored procedure a subroutine, which is as good a definition as any. Stored procedures (SP from here on) can be used to:<br />
<br />
* Encapsulate complex processing<br />
* Update a table. Sure, UPDATE does that, but then the end user (web developer) needs to be familiar with your database.<br />
* Return data to SQL such as a result set. Again, isolate the database from the UI. A result set can consolidate columns from several tables, in essence hiding the JOINS from the ultimate end user of the data.<br />
* Provide a service to SQL like RTVSYSVAL.<br />
<br />
IBM have a 2006 Redbook entitled [http://www.redbooks.ibm.com/abstracts/sg246503.html Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries]. It's old but it provides a foundation for understanding stored procedures.<br />
<br />
SPs are an inherent part of DB2; they are part of the database. On other platforms, the logic section of SPs is written using SQL. That's a choice on DB2 for i as well, and this is called an SQL Stored Procedure. We have another choice as well, and that is to write the logic using an HLL like RPG. This is called an External stored procedure. In either case, you register a SP with DB2 via the SQL '''CREATE PROCEDURE''' statement. To use a stored procedure in your SQL, use the '''CALL''' statement.<br />
<br />
While all programs on the IBM i can be called as stored procedures, CL commands are not programs. The way around this is to use QSYS.QCMDEXEC() or QSYS.QCMDEXEC2() stored procedures.<br />
<br />
<br />
An external SP interfaces with DB2 via the parameter list. There are several parameter styles, depending on whether you will need to handle NULL values or not. <br />
<br />
TODO: <br />
* Example tables<br />
* Example SQL SP<br />
** One return variable<br />
** Result set<br />
* Example RPGLE SP<br />
** One return variable<br />
** Result set<br />
* Multiple parameter list usage<br />
** GENERAL<br />
** WITH NULLS<br />
* Canonical documentation for parameter lists<br />
* Consume a result set. Java?</div>Zippy1981https://wiki.midrange.com/index.php?title=Category:SQL_Clients&diff=11992Category:SQL Clients2015-11-24T02:55:33Z<p>Zippy1981: Created category.</p>
<hr />
<div>These are tools for executing SQL DDL and DML on an IBM i system.<br />
<br />
== Offical IBM clients ==<br />
<br />
* System navigator<br />
* RUNSQLSTM<br />
* STRSQL<br />
<br />
== JDBC Clients ==<br />
These use the [http://jt400.sourceforge.net/ JTOPEN] JDBC driver.<br />
<br />
* [http://www.sql-workbench.net/ SQLWorkbench/J] - Very popular, and very well supported<br />
* [http://squirrel-sql.sourceforge.net/ Squirrel SQL]<br />
<br />
== ODBC Clients ==<br />
**TODO**: Add some here<br />
<br />
* SQL Server - You can use your IBM i as a linked server. See http://www-01.ibm.com/support/docview.wss?uid=nas8N1014514</div>Zippy1981https://wiki.midrange.com/index.php?title=Twitter&diff=11991Twitter2015-11-24T02:45:56Z<p>Zippy1981: Added my twiter hash tag.</p>
<hr />
<div>[[Category:Social Media]]<br />
Below is a list of people on Twitter who work on the midrange platform.<br />
<br />
* [http://twitter.com/fallingrock @fallingrock] - [[User:David|David Gibbs]]<br />
* [http://twitter.com/MikeWills @MikeWills] - [[User:Koldark|Mike Wills]]<br />
* [http://twitter.com/starbuck5250 @starbuck5250] - [[User:Starbuck5250|Buck]]<br />
* [http://twitter.com/kisluvkis @kisluvkis] - Julie G.<br />
* [http://twitter.com/tduncan @tduncan] - [[User:Tduncan|Tom Duncan]]<br />
* [http://twitter.com/torriatte @torriatte] - [[User:Torriatte|Chuck]]<br />
* [http://twitter.com/ericjooka @ericjooka] - Trevor Perry<br />
* [http://twitter.com/johntearl @johntearl] - John Earl<br />
* [http://twitter.com/JonParis @JonParis] - Jon Paris<br />
* [http://twitter.com/joelcochran @joelcochran] - Joel Cochran<br />
* [http://twitter.com/bobcozzi @bobcozzi] - Bob Cozzi<br />
* [http://twitter.com/Sys_i_geek @Sys_i_geek] - Pete Helgren<br />
* [http://twitter.com/DrFranken @DrFranken] - Larry Bolhius<br />
* [http://twitter.com/MikeyPEI @MikeyPEI] - Mike Pavlak<br />
* [http://twitter.com/commonug @COMMONUG] - [http://www.common.org COMMON]<br />
* [http://twitter.com/fromwhereistand @FromWhereIStand] - Don R<br />
* [http://twitter.com/col250401 @col250401] - Colin Williams - [http://www.as400blog.blogspot.com/ as400blog] <br />
* [http://twitter.com/pla1 @pla1] - Patrick L Archibald<br />
* [http://twitter.com/dkahn400 @dkahn400] - Dave Kahn<br />
* [http://twitter.com/f_lombardo @f_lombardo] - Franco Lombardo<br />
* [http://twitter.com/nigelvictorrees @nigelvictorrees] - Nigel Rees<br />
* [http://twitter.com/eyeonpower @eyeonpower] - Raymond Phillips<br />
* [http://twitter.com/feyrertech @feyrertech] - Christopher Feyrer<br />
* [http://twitter.com/Steve_Will_IBMi @Steve_Will_IBMi] - Steve Will<br />
* [http://twitter.com/martyacks @martyacks] - Marty Acks<br />
* [http://twitter.com/BizDevTalk @BizDevTalk] - An upcoming podcast and programmer-only talk run by [http://twitter.com/MikeWills @MikeWills]<br />
* [http://twitter.com/alanseiden @alanseiden] - Alan Seiden<br />
* [http://twitter.com/petem59 @petem59] - Pete Massiello<br />
* [http://twitter.com/IncredibleUniv @IncredibleUniv] - Jack Hallaran <br />
* [http://twitter.com/BigSDavid @BigSDavid] - David Wilson <br />
* [http://twitter.com/zippy1981 @zippy1981] - Justin Dearing<br />
Are you on Twitter? Add yourself to the list!<br />
<br />
==Hash Tags==<br />
* [http://twitter.com/#search?q=%23IBMI #IBMi] - used to identify items of interest to the [[IBM i]] community.<br />
* [http://twitter.com/#search?q=%23COMMONUG #COMMONUG] - identifies tweets related to the [[COMMON]] user group.<br />
<br />
==External links==<br />
* Search Twitter for [http://search.twitter.com/search?q=iSeries '''iSeries''']<br />
<br />
==Send Tweets from the System i==<br />
* Send a tweet [[SNDTWTMSG|using cURL]]<br />
* [http://www.kisco.com/sndtweet/summary.htm SNDTWEET]</div>Zippy1981https://wiki.midrange.com/index.php?title=Sql_convert_from_dds&diff=11990Sql convert from dds2015-11-24T02:45:46Z<p>Zippy1981: Added an example</p>
<hr />
<div>WORK IN PROGRESS<br />
- please help by editing this document<br />
- include code/specific commands where needed<br />
<br />
<br />
SQL described tables work better (faster and more efficiently) than DDS described files.<br />
<br />
How to convert from DDS to SQL without breaking existing programs...<br />
<br />
Big picture...create a new SQL table to hold the data, build indexes across this new table, rebuild the physical file DDS as a logical file, change the logicals to point at new physical.<br />
<br />
#Retrieve the SQL for your physical table using API or iNavigator.<br />
##change the name of the table to match your company standards<br />
###for example, physical file MSPMP100, new SQL name MSPMP100SQ<br />
##use old format name for new table <br />
#Retrieve list of key fields used across the file<br />
#Build indexes using longest number of key fields to shortest<br />
#Rebuild the physical file DDS to be a logical file<br />
#Change the logical files to point to new physical<br />
##if logical uses select/omit change to use dynslt<br />
##include a index across select/omit fields<br />
#Compile all logical files (including the "old physical")<br />
<br />
<br />
== Stored Procedure GENERATE_SQL() ==<br />
<br />
Beginning with Release 7.1 TR8 (and Release 7.2) the '''GENERATE_SQL()'''stored procedure is integrated in the '''QSYS2''' schema.<br />
This procedure allows DDS sources to be converted into SQL and stored either as stream file or in a source physical file member.<br />
Detailed Information can be found under the following Link:<br />
[https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.GENERATE_SQL%28%29%20procedure QSYS2.GENERATE_SQL() procedure]<br />
<br />
It has two modes of operations. It can write SQL to a source member or return it as a recordset, which creates a source member in QTEMP behind the scenes. Here is an example usage of common parameters:<br />
<br />
CALL QSYS2.GENERATE_SQL(<br />
'TABLEPF', 'BARLIB', 'TABLE', -- We want to script out the object TABLEPF from schema (libary) BAR of type table (physical file)<br />
-- 'QSQLSRC', 'BAR', 'TABLEPF', -- Uncoment to write this to a source member instead of outputing as a resultset<br />
STATEMENT_FORMATTING_OPTION => 0, -- Setting to 1 will cause SEU's syntax highlighting to be confused.<br />
REPLACE_OPTION => 1, -- Set to 0 to append to the source member as opposed to overwriting it.<br />
CREATE_OR_REPLACE_OPTION => 1, -- If the system supports CREATE OR REPLACE syntax on this object, generate it.<br />
CONSTRAINT_OPTION => 1, -- Script out constraints<br />
DROP_OPTION => 1, -- Precede the CREATE statement with a DROP DDL statement.<br />
NAMING_OPTION => 'SQL',<br />
QUALIFIED_NAME_OPTION => 0 -- Set to 1 to fully qualify all objects in the generated DDL<br />
);<br />
<br />
<br />
It should be noted that you may use the % wildcard in the object and library parameter to generate DDL for multiple objects. However, you will need to set the REPLACE_OPTION parameter to 0 in that case or you will only get the last object generated. Also, the source member you intend to write to must exist.<br />
<br />
===Troubleshooting===<br />
<br />
If QSYS2.GENERATE_SQL() returns an error, its usually not helpful. However the joblog generates helpful messages. You can retrieve the joblog via the usual greenscreen methods or [http://www.rpgpgm.com/2015/06/using-sql-to-get-information-from-job.html select * from table(qsys2.joblog_info('*')) a].<br />
<br />
==Categories==<br />
[[Category:SQL|DDS to SQL DDL]]<br />
[[Category:DDS|DDS to SQL DDL]]<br />
{{stub}}</div>Zippy1981https://wiki.midrange.com/index.php?title=Category:5250_Emulators&diff=11943Category:5250 Emulators2015-06-02T02:45:47Z<p>Zippy1981: Updated the list.</p>
<hr />
<div>[[Category:Categories]]<br />
There are a number of commercial and open-source 5250 emulators available. Here is a short list:<br />
<br />
Open Source<br />
* [[tn5250]] -- (License: LGPL) [[Linux]]/Unix curses based 5250 emulator. The tn5250 project also produces lib5250, the backend for several other emulators. Also runs under Windows.<br />
* [http://tn5250j.sourceforge.net/ tn5250j] -- (License: GPL) [[Java]] swing based 5250 emulator.<br />
* [[x5250]] -- (License: GPL) X11 based 5250 emulator which uses lib5250.<br />
* [http://xtn5250.sourceforge.net/ xtn5250] -- (License: Apache) [[Java]] swing based 5250 emulator.<br />
* [https://github.com/tommys-place/extjs5250 extjs5250] -- (License GPLv2) extjs front end for tn5250j.<br />
* [https://github.com/cloose/cute5250 cute5252] -- (License BSD 2 clause)<br />
* [http://sourceforge.net/projects/vb5250/ vb5250] -- (License: GPLv3) VB.NET based 5250 emulator.<br />
* [http://sourceforge.net/projects/qtn5250/ qtn5250] -- (License: Public Domain) QT based TN5250 generator with Mac and Linux builds.<br />
<br />
Commercial<br />
* [[iSeriesAccess]] -- Windows based emulator from [[IBM]]<br />
* [http://www.netmanage.com/products/rumba/index.asp Netmanage Rumba]<br />
* [http://www.synapse.com Synapse]<br />
* [http://www.mochasoft.dk Mochasoft]</div>Zippy1981