JPLTools Home Page  

Ma boite à outils  Dans cette boite à outils, je range les logiciels dont j'ai eu besoin et qui m'ont manqué jusqu'à ce que je les trouve ou que je les fabrique.
jpl

Contact		 J'espère que vous aurez autant de plaisir à utiliser ces outils que j'en ai eu à les ecrire. 
Si cela vous a plu, envoyez moi un petit mail, ça me fait toujours plaisir de savoir ce que vous faites avec mes outils. 
Et s'il vous manque quelque chose, demandez, on ne sait jamais. 

I hope that you will have as much pleasure to use these tools as I had  to write them. 
If it pleased you, send me a little email, that always makes me happy to know what is done with my tools. 
And if you are lacking something, ask, you never know.

I'm not very fluent in English.If any statement is not correct, feed back a better statement.


Index



my code generator

The Frame generatorFrame Generer 90% du code RPG et 95% du code DSPF, c'est possible
Generate 90% of RPG code and 95% of DSPF code, it is possible



framework for EXCEL 2003

Excel powered by SSML2003 XLXML produire avec RPG un fichier IFS directement lisible par Excel : la puissance de SpreadSheetML, le dialecte XML de Office 2003. Ce format ne supporte pas les macros: sécurité garantie.
RPG produced IFS file directly readable by excel : the power of SpreadSheetML, the XML format of Office 2003 Excel spread sheets.This format does not sup^port macros. Security is guaranteed.
Run sql and export output to excel SQL2XLS export direct DB2 vers Excel, au format Excel XML SSML 2003 (basé sur XLXML ci-dessus)
direct export DB2 to Excel, under XML Excel SSML 2003 format, compatible Open Office 2. SBCS version and UNICODE version



IFS tools
CHKLNK vérifier l'existence d'un fichier ou d'un repertoire IFS
check existence of IFS file or directory
DSPIFS, DMPIFS Affiche sur écran ou en soule le contenu hexa d'un fichier IFS
Shows on screen or in spool the hexadecimal value of an IFS file
Retrieve a complete IFS file nameRTVIFSNAM Récupère le nom complet d'un fichier IFS. Un accessoire de mes outils RPG-TO-IFS
Retrieve a complete IFS file name. A companion tool for my RPG-TO-IFS tools
CRTSTMF  Crée un fichier IFS, avec le bon CCSID
 Create a stream file on IFS ... with the good CCSID 



SQL tools
EXECSQL exécuter une pile de commandes SQL
run an SQL stack
EXECUTESQL lire, transformer, ajouter, enlever, corriger des données dans un programme CL
read, transform, add, remove, update data inside a CL program : all with SQL statements
Retrieve SQL source statement RTVSQL Récupère le source DDL d'un objet DB2-SQL
Retrieve DDL source of any DB2-SQL object
Sql Pivot Table comment calculer un tableau croisé dynamique en sql ?
How to build a dynamic pivot table with sql ?



framework for PDF

How to make a PDF file with RPG PDF création de PDF en RPG. Totalement graphique.
RPG builder for PDF. Build totally graphic pages
Export a spool to a PDF file SPLF2PDF convertit un spoule en PDF
convert a spool into PDF



Published articles

 
User Defined table Function  I've writen an article on UDTF for SystemiINetwork : Build a New Interface to Legacy Applications with SQL UDTFs
Procedure & performance I had a performance issue while converting a subroutine to a subprocedure. I searched through my logic looking for reasons, but found nothing wrong. The issue was elsewhere.

In this article I show you the pitfall that I discovered and how to avoid it while improving your code to use subprocedures, one of ILE's prettiest features.

  discoveries about the performance of RPG subprocedures.

Q: Concerning your article "Procedures and Performance" (article ID 54505 at SystemiNetwork.com) maybe you could improve the performance of your procedure using the STATIC keyword in the local variable definition?

A: Good question. I had the question on the tip of my tongue, but at the time that I wrote the article my point wasn't about optimizing the code, it was to show how painful my experience was. Done! Now it's time to show how to use the STATIC keyword.

more on subprocedure performance discoveries
2008-06-16
 5250 modernization strategies		 Moderniser : pourquoi, comment, quelle rentabilité
 Modernization strategies for a quick ROI
2008-08-31
Dynamic Where and Order-By		 Comment coder un select sql dynamique, avec un where dynamique et un order by dynamique.
 How to code a completely dynamic select sql statement, with a dynamic where and a dynamic OrderBy
2011-05-04
When CCSID Constants Vary, Part I

Learn how one simple character can make a program go crazy.

Several years ago, I wrote an email processing program for the System i and posted the source on my Web site. Several people have downloaded the code, compiled the program, and run it without any problems…until recently. For a few months now, people have been reporting that the program crashes. The program MAIL, which worked perfectly for several years, suddenly went haywire.

Read More >>

2011-06-01

When CCSID Constants Vary, Part II 		Don't let a small thing like a literal string completely ruin your multi-national program.

The variations of CCSID are a vast topic and are in fact the main issue to consider during globalization of an application. You will find many pages on the Web on this topic (search for Unicode, CCSID, globalization, or national language support). Some of the most interesting are the ones from the IBM i Information Center.

In my previous article, I showed how a simple line of RPG code can turn a program haywire just because the JOB CCSID has changed. In this article, I will explain how to handle literal strings, which are sensitive to CCSID variations.

Read More >>
2011-07-06
When CCSID Constants Vary, Part III

Learn some tips and tricks to correctly handle literal constants.

My first article described how a CCSID variation can turn a program crazy. My second article described the solution, which requires correctly handling literal constants. In this last article, I share some tips and tricks I found while solving the bug with the @.

Read More >>
2011-12-09
Analyze Your Programs And Applications, Part I

In the first of this series of TechTips, find information about each object in each library.

Recently, a friend called me for help. His boss asked him two questions:

  • How much lost or forgotten old RPG or CL do we have?
  • What is the status of the "general review of all the ILE programs" project?

And of course, the boss didn't want to invest in software for analyzing cross-references. "With all the commands in the OS, there is at least one that gives this info, isn't there?"

Read More >> 
2012-02-04
Analyze Your Programs And Applications, Part II

The first part of this article series showed how DSPOBJD can easily provide statistics to manage a general recompilation project. But it only touched the surface of all the things you get with the DSPxxx commands.

Read More >> 
2012-07-06
Audit Your Exit Points

Suppose it's the beginning of a new year, and the first time the human resources manager logs into payroll, he unknowingly sends the file of annual salaries to an email address it shouldn't go to, by directly Telnet-ing port 25, something discreet and undetectable.

Read More >>
2012-08-10
Analyze Your Programs And Applications, Part III
 Fill the gap between modules and ILE programs for a full panorama of your ILE application.

Part I and Part II of this series have shown how some system commands can be used to dissect in depth one application, without the source code. But these analyses suffer from a lack of information for ILE programs—namely, the link between the program and embedded modules. Read More >>

2022-01-06
DB Modernization Plan
 A step by step modernization plan to modernize existing database, allowing creation of new application on a normalized database with as less as possible impacts on existing application.



framework for SYLK - a text format for excel up to office 2000 (outdated, look at Excel powered by SSML2003, still accurate in 2022 XLXML)
Excel powered by Multiplan ... (outdated)SYLK2 modèle de programme de création de fichier Excel
Excel sheet creation sample program
Excel powered by Multiplan ... (outdated) SQL2SYLK Export direct DB2 vers Excel
direct export DB2 data to Excel



Miscanellous tools
Trucs et Astuces pour AS400 Ces rubriques contiennent beaucoup plus d'extrait de code que de programmes entiers. Elles visent à résoudre un problème particulier sans chercher à apporter une solution globale.
/COPY /copy utilisés dans mes outils. Cette page contient aussi jpltools.zip, le savf avec tous les sources
/copy used in my tools. This page contains also jpltools.zip, the savf with all the sources
BRKMSG afficher les break-messages sur la ligne de statut, san sinterruption
Shows the break-message in the status line, without interruption
CHGENV, CHGLIBLIST changer la liste des bibliothèques d'un travail
Change the job library list
CHGOBJATR changer la référence au fichier source d'un objet, la version, le nom de produit, ...
Change the reference to the source file name of any object, the version number, the pruduct id, ...
FINDMBR retrouve un membre même s'il n'est pas dans le premier fichier
retrieve a member even if it is not in the first file
INDENT Free-RPG numérote les boucles, indente le code Free-RPG
renumber RPG loops, indent Free-RPG code
JPL020 base de programme + écran (simple, sous-fichier de données, sous-fichier de messages)
basic program + screen (simple, data subfile, message subfile)
QSYSOPR Surveiller les messages de QSYSOPR
QSYSOPR messages supervisor
MAKE mémorise et reproduit les commandes de compilations de tous les objets
save and rebuild all the compilation commands for any object
OBJUNLCK déverrouille un objet
unlock an objet
The RPG Bitmap maker Création de fichier BMP en RPG. Pour la beauté du geste
Create a BMP file from RPG. For the fun only.
 Send a Mime Mail MAIL envoyer à plusieurs destinataires un document au format texte ou html et plusieurs pièces jointes
send to many receivers a body in text or html format and many documents attached 
Work with Jobs
WRKJOBS and SLPPGM		     WRKJOBS remplace avantageusement WRKJOB : filtres variés
SLPPGM liste les programmes dormants d'un travail
replace WRKJOB advantageously: varied filters
SLPPGM list of any job's dormant programs
Cleanup old spools encore un autre nettoyeur de spools.
yet an other spool cleaner
2013-05-05
ZLIB127, The savf with all the sources 		Compilation pour IBMi de ZLIB 1.2.7.  + un exemple  RPGLE
COmpilation for IBMI of ZLIB 1.2.7, + an RPGLE example
the RTV* series
 RTVAUTL : retrieve every objects protected by an authorization list, allow to generate easily the CHGAUT and GRTOBJAUT to transfert protection to another AUTL (see joblog)
implement api formats ATLO0100 and ATLO0110

RTVCMD : retrieve description of commands, implement format CMDI0100

RTVEXIT : retrieve every exit program of every exit point, prepare a query to detect unattended exit programs
implement api formats EXTI0100, EXTI0200, EXTI0300

RTVFD : retrieve a lot of information about a file, produce a PRTF, implement api QDBRTVFD and it's dozen of embedded binary structures

RTVIFSNAM : make a stream file name

RTVJRN: retrieve informtion about journals, implement api formats RJRN0100 , RRCV0100

RTVMSGSRC: generate a CHGMSGD for each message of a message file

RTVMSGF: generate the CLLE source code for a *MSGF

RTVPGM: retrieve most informations of MODULE, PGM and SRVPGM. Implement api formats MODI0100, MODL0100, MODL0200, MODL0300, MODL0400, MODL0500, PGMI0100, VEWL0100, SPGI0100, PGML0100, PGML0200, PGML0300, PGML0400, PGML0500, SPGL0600, SPGL0700, SPGL0800

RTVPRF: retrieve information on all profiles

RTVSAVF: retrieve description of a save file. Implement SAVF0100 format

RTVSFD: retrieve the informations produced by a SQL DESCRIBE

RTVSQL: implement SQLR0100


Trouvé sur le net / Found on the net

Some API prototypes & samples http://www.think400.dk/apier.htm

Prototyping C functions  http://www.scottklement.com/rpg/callc.html

 

Outils AS400 par famille - SystemI5 tips and tricks by family

 Family
Summary
 sql
SQL tips and tricks 
  • the system one-row table
  • the best-fit join on many keys
  • improve CPYTOIMPF
  • transform to xml, only wih sql
  • horizontal count (improved at link sql pivot)

SQL Visual Explain

  • Where is the native SystemI5 graphic query analyser ? it is in iSeries Navigator.

Tableau croisé SQL Pivot Table

  • how to build a pivot table with SQL & RPG

How to call RPG from a web page

  • how to run SQL scripts from a web page. Toolbox : native OS400 softs only; ie http-server-powered-by-apache and net.data macro inerpreter

EXECSQL runs a SQL stack

  • How to run a SQL script, with parameters, don't need compilation

ExecuteSQL - the update-file CLP command

  • how to run any sql phrase into a CLP or CLLE
  • compare STRQMQRY, RUNSQLSTM, EXECSQL & EXECUTESQL
  • show how to use a dictionary to create table : with create table xxx as (select ...)
  • reference to same kind of tool provided by OS400 (native call qzdfmdb2 and qsh command DB2)
  • compare extraction tools (iSeries DTF, SQL2SYLK) and method for correct column naming

Build a New Interface to Legacy Applications with SQL UDTFs

 extraction tools
SYLK2 produce an Excel sheet from an RPG program
  • how to build an excel file with RPGLE. Shows how to work with
  •  
    • Style of cell: of text, of number, of date, of hour 
    • Police (font, size, bold, italic, underlined) 
    • Border and grey 
    • Format of cell: left, centered, right 
    • height of line 
    • width of column 

SQL2SYLK - To produce an AS400 file directly readable by Excel is at your hand

  • how to run a sql select and transform it into an excel file, based on SYLK2

XLXML, the RPG SSML (Spread Sheet XML)

  • How to build an excel file with RPGLE, based on Office 2003 XML format. Not easy to master, but extremely powerful

Run sql and export output to excel uses Excel XML SSML2003 format 

Export a spool to a PDF file

Export a spool to a XML or HTM file

Publishing graphic data How to make a PDF file with RPG

The RPG Bitmap maker

Send a Mime Mail
Coming soon
 
   

Logiciels PC

Trucs et Astuces pour le Web Ces rubriques contiennent beaucoup plus d'extrait de code que de programmes entiers. Elles visent à résoudre un problème particulier sans chercher à apporter une solution globale.
Album Express Ce logiciel a pour but de construire un album photo le plus simplement possible à partir d'une liste d'images JPG
VbEdit 2 éditeurs DHTML ... livré avec leurs sources. un en VB6, un en HTML

Outils SAP

Trucs et Astuces pour SAP Ces rubriques contiennent beaucoup plus d'extrait de code que de programmes entiers. Elles visent à résoudre un problème particulier sans chercher à apporter une solution globale.
Trouvé sur SearchSAP.com - find on SearchSAP.com and other sites
ABAP Program types
Change logon screen
Debug background process
Debug pop-up screen
Download internal table data into Excel
Debugging : Export internal table to Excel for analysis
Go to your URL using ABAP
How to handle time zones in ABAP
Inserting photos into SAPscripts
Logos on screen
Read an area from an Excel sheet and write out a diagnostic
Send background job failure alerts by email
Use Excel instead of Tab delimited text in BDC programs
the ABAP official reference http://help.sap.com/saphelp_47x200/helpdata/en/d3/2e974d35c511d1829f0000e829fbfe/frameset.htm
the abap beginner forum https://www.sdn.sap.com/irj/sdn/thread?threadID=36946

This tool is a free source text; you can redistribute it and/or modify it as much as you need. This tool is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY :

Transforming these source text into executable software is made under your responsability.

THIS SOURCE TEXT IS PROVIDED ''AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE PROVIDER OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
OF THE USE OF THIS SOURCE TEXT, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.