SQL select records in special order

The plain SQL statement “SELECT * FROM mrcdct16” returns:

RETRN   SEQNO  RECRD        FIELD  
 2,710      6   ORDDETF     ORDNUM 
   100      5   INCIDENTR   EMP#   
   102      7   ORDDETHISR  ORDNUM 
   106      7   ORDERHEADR  CUSNO  
   101      5   INCIDENTR   EMP#   
     1      4   INCIDENTR   EMP#   
 2,740      7   ORDDETHISR  ORDNUM 
   103      5   INCIDENTR   EMP#   
   105      5   INCIDENTR   EMP#   
     6      7   ORDERHEADR  CUSNO  
   107      7   ORDERHEADR  CUSNO   

We want to select records with the first letter in FIELD to be E, C, O. We can use SQL

SELECT * FROM mrcdct16 order by position (substring(field, 1, 1) in 'ECO')

100      5   INCIDENTR   EMP#           2
101      5   INCIDENTR   EMP#           2
  1      4   INCIDENTR   EMP#           2
103      5   INCIDENTR   EMP#           2
105      5   INCIDENTR   EMP#           2
106      7   ORDERHEADR  CUSNO          2
  6      7   ORDERHEADR  CUSNO          2
107      7   ORDERHEADR  CUSNO          2
710      6   ORDDETF     ORDNUM         2
750      6   ORDDETF     ORDNUM         2

Or select them with 2 letters in FIELD as EM,OR,CU

SELECT * FROM mrcdct16 order by position (substring(field, 1, 2) in 'EMORCU')

Then we get:

  100      5   INCIDENTR   EMP#           2 
  101      5   INCIDENTR   EMP#           2 
    1      4   INCIDENTR   EMP#           2 
  103      5   INCIDENTR   EMP#           2 
  105      5   INCIDENTR   EMP#           2 
2,710      6   ORDDETF     ORDNUM         2 
2,750      6   ORDDETF     ORDNUM         2 
  106      7   ORDERHEADR  CUSNO          2
    6      7   ORDERHEADR  CUSNO          2
  107      7   ORDERHEADR  CUSNO          2

Javascript closure and setTimeout

When we need to pass parameters to function called in setTimeout sometimes we have to use Javascript closure.

Example in function onmouseover we normally call doajax function using this.doajax. In order to achieve a time delay and pass this to doajax we set up a closure using variable callDoajax

<script type="text/javascript" src="prototype.js"></script>
var AJAX_DELAY=2000; 
function ALinkObj(elementName, options) {
  this.element = document.getElementById(elementName);
  if (!this.element) throw new Error(elementName + ' not found');
  this.element.alinkobj = this;
  this.options = options || {};
  this.ajaxboxid = this.options.ajaxboxid || "ajaxbox";
 
  Event.observe(this.element, "mouseover", this.onmouseover.bind(this));
  Event.observe(this.element, "mouseout", this.hidebox.bind(this));
}

ALinkObj.prototype.onmouseover = function() {
  this.cursorIn = 'y';

  //this.doajax();  //without time delay

  //use closure to pass this object
  var callDoajax = this.doajax;
  setTimeout(callDoajax.bind(this), AJAX_DELAY);
  
}

ALinkObj.prototype.doajax = function() {   
  if (this.cursorIn == 'n') return; 
  new Ajax.Request(this.element.title,{ method: 'get',
      onSuccess:displayData.bind(this), 
      onFailure: displayErr.bind(this) } );
}

Javascript paper: http://www.jibbering.com/faq/faq_notes/closures.html

SQL “EXCEPTION JOIN”

You can use EXCEPTION JOIN to select records in a table that do not have matching records in another table. For example:

Table DCT14 has records:

RETRN         RETDS            
   10         Adv Rec Lvl Sec  
   50         Adv APP          
   20         APP 1            
   30         APP 2            
   40         APP 3         

Table DCT16 has records:

RETRN      SEQNO      RECRD 
    10        1       ORDERH
    30        1       ABC   

SELECT * FROM dct14 a EXCEPTION JOIN dct16 b USING (retrn) returns:

RETRN         RETDS    
    50        Adv APP  
    20        APP 1    
    40        APP 3      
 

SELECT * FROM dct14 a JOIN dct16 b USING (retrn) returns:

RETRN         RETDS            
   10         Adv Rec Lvl Sec  
   30         APP 2          

Work with Job Queue

Use WRKJOBQ to display all job queues. A job queue (in a library) needs to be associated with a sub-system so that when you send a job to that job queue it will go to that sub-system.

You can use ADDJOBQE to add a job queue to a sub-system.

WRKJOBQ output:

                            Work with All Job Queues                            
                                                                                
 Type options, press Enter.                                                     
   3=Hold   4=Delete   5=Work with    6=Release                                 
   8=Work with job schedule entries   14=Clear                                  
                                                                                
 Opt     Queue          Library          Jobs     Subsystem      Status         
         BSCTCP         BSCCDROM10          0     BSC             RLS           
         MRCTCPSRV      MRCAPPLCGI          0                     RLS           
         MRCTCPSRV      MRCAPPLLIB          0     MRCTCPSRV       RLS           
         MRCTCPSRV      MRCAPPLREL          0                     RLS   
         QBASE          QGPL                0                     RLS           
         QBATCH         QGPL                0     QBATCH          RLS           
         QFNC           QGPL                0                     RLS           
         QINTER         QGPL                0     QINTER          RLS           
 

Set JDK level on AS400/iSeries for RPG/Java

When your RPG program calls Java you can set the QIBM_RPG_JAVA_PROPERTIES environment variable to specifically set the java version for your Java program. You have to do this before the JVM is started.

ADDENVVAR QIBM_RPG_JAVA_PROPERTIES VALUE('-Djava.version=1.4;')

Javascript function context

<html>

<head>
<script type="text/javascript" src="prototype.js"></script>
</head>
<body>
<h1> Test Javascript function context </h1>
<br>

<DIV id="testpad1"> Test Events in a DIV </DIV> <br/>
<DIV id="testpad2"> Test Events in a DIV </DIV> <br/>
<DIV id="testpad3"> Test Events in a DIV </DIV> <br/>
<DIV id="testpad4"> Test Events in a DIV </DIV> <br/>

<script type="text/javascript">
window.x = 'win';
var display = function () {
alert(this.x) ;
}

var obj = new Object();
obj.x = 'obj...';

display(); // dsp 'win'
display.call(obj); // dsp 'obj...'

var pad = $('testpad1'); // assign x property to this element
pad.x = 'pad!';

Event.observe($('testpad1'),"click", display) ; //dsp 'pad!'

Event.observe($('testpad2'),"click", display) ; //dsp 'undefined'

Event.observe($('testpad3'),"click", display.bind(obj)) ; //dsp 'obj...'

//Cannot do this. it does not assign display to testpad4
Event.observe($('testpad4'),"click", display.call(obj)) ;

</script>

</body>
</html>

Use of the QAQQINI file on AS400/iSeries

Beginning at V4R4, the QAQQINI file (query options file) was introduced. This file replaced the QQQOPTIONS data area in addition to providing other functions, including Service Query. While SRVQRY is still supported for R440, use of the QAQQINI file is much easier, as there is no need to transfer the tool to a customer's system. This document describes only the publicly documented options.

To set up and use a QAQQINI file, do the following:

1. Use the CRTDUPOBJ command to make a copy of the QAQQINI file located in QSYS. The file name must be QAQQINI. The file can be placed in QUSRSYS; however, all jobs check this library for the existence of the file and uses it if it exists in that library. Therefore, do not put the file in this library unless you want to use it for all queries on the system. Use of this file could cause degraded performance. Therefore, you need to be very careful before allowing all queries on the system to be impacted by it. Do not use the CPYF command to make this copy. Creating a duplicate is required because a trigger is associated with the file. CPYF does not maintain the trigger association on the copy. The trigger is needed to ensure correct operation. Making a copy of the file by any means other than the CRTDUPOBJ command or save/restore causes unpredictable results.

2. If the data was copied together with the file, the publicly documented options should already be in the file. The file must be updated to reflect the parameter options for parameters that already exist in the file. Using SQL or Operations Navigator is probably the easiest method. You can use DFU, but you must create a DFU that contains only the first two fields. The third field is a comment field, the use of which is optional, but it is of a type that DFU cannot handle. A program could also be written to perform the updates. To use options that are not documented to the public, records must be inserted. The values can be entered in either uppercase or lowercase. The trigger program converts them to uppercase and also validates that they are valid values. Update must be used to change a value. When using SQL, remember the comparison is case sensitive and, therefore, must be typed in uppercase since the trigger ensures only uppercase letters are inserted. There are two required fields. The first, QQPARM, is the parameter being set. The second, QQVAL, is the value for the parameter. The third field, which is optional, is QQTEXT.

3. Once the options are set, use the CHGQRYA command to specify the library where your copy of the QAQQINI resides (assuming you are not using QUSRSYS, it is already the default for all queries).

Descriptions of the Publicly Documented Options:

http://www-1.ibm.com/support/docview.wss?uid=nas19fbb11f1725466578625694c007511a1