FAQ Database Discussion Community


JT400 - Display content of a SpooledFile

java,ibm-midrange,jt400
I try to display the content of a SpooledFile with the library jt40. I use this code : public static void printJogLog(AS400 as400, Job j) { SpooledFile spooledFile = new SpooledFile(as400, "QPJOBLOG", 1, j.getName(), j.getUser(), j.getNumber()); try { PrintParameterList printParms = new PrintParameterList(); printParms.setParameter(PrintObject.ATTR_WORKSTATION_CUST_OBJECT, "/QSYS.LIB/QWPDEFAULT.WSCST"); printParms.setParameter(PrintObject.ATTR_MFGTYPE, "*WSCST"); PrintObjectPageInputStream is =...

ISeries Tables/Views not showing up in Crystal Reports 2013

crystal-reports,ibm-midrange,iseries-navigator
I have an iSeries server running v5r4. I have several tables and views that I have created over the past couple of years on the server. I have used them with no problem in the past, yet suddenly most of the tables and views in a Schema that I created...

How to create UDF in AS400

ibm-midrange,iseries-navigator,udf
I have this code of UDF that stars with create function... But I have NO idea where to put it inside the iseries navigator screen. can anyone help me? Google isn't helping...

REPLACE function not available in AS/400 DB2

sql,ibm-midrange,db2400
I am trying to replace or remove the “ - “ character from read data in DB2/400 IBM System i mid-frame. Its very common in MS-SQL to make replacement using REPLACE function but here in DB2/400 V5R2 it seems that this function is not available. Could anybody help me in...

Hikari connections and active AS400 jobs

java,database,ibm-midrange,hikaricp
I am using Hikari connection pool manager to query some tables in an AS400 machine. I've set a minimum of 1 connection pool connections and a maximum of 10. I am querying 5 tables at the same time. The problem is that despite using HikariDataSource getConnection() method before querying and...

How to add editing and validation rules to CL programming? (As400)

ibm-midrange
please note this is a 'expand your knowledge' (optional task) for college, not an assignment I have a physical file called INVFILE and a logical file called INVFILE1 I have the CL program called INVCL99 this these are my columns for INVFILE1 R INVFILE 1 PARTDESC ONHAND UNITCOST STOCK PARTNUM...

See actual code of function in DB2 for IBM i

db2,ibm-midrange,dbvisualizer,jt400
Hello guy I create a function in DB2 UDB for AS/400 version 07.01.0000 V7R1m0 I use a windows with dbvisualizer to connect the server. My function is... CREATE FUNCTION JVAOBJ.BNOWPAPOL(POL VARCHAR(10)) RETURNS DECIMAL(7,7) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA RETURN ( SELECT CASE WHEN NUM IN (1,2) THEN 0.3...

Is there a method to set the value for the Return code (RTNCDE) parameter of the Retrieve Job Attributes (RTVJOBA) command

ibm-midrange
The following documents that the Return Code (RTNCDE) value available from the Retrieve Job Attribute (RTVJOBA) command is returned as DECIMAL(5, 0) and later clarifies in the thread-safety information\table that the value is actually called [more accurately to be referred to as] the Program Return Code and that the value...

Dynamic CTE's as part of a SProc in DB2/400

sql,dynamic,common-table-expression,ibm-midrange,db2400
I'm trying to write a SProc in db2/400 in a V7R2 environment which creates a CTE based on the parameters passed. I then need to perform a recursive query on the CTE. I'm running issues into creating and executing the dynamic CTE. According to http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzpreph2.htm the prepare statement does not...

How do i set up datasource on Jboss 6 EAP for as400 db2?

java,jboss,ibm-midrange,jboss6.x,db2400
I am trying to setup a jboss 6.3 data-source to an ibm as400 jboss 6.3 is running on a windows server 2012 VM java 1.8.0_31 64 bit i am getting this error in the console on start up of jboss 1 4:02:09,332 INFO [org.jboss.as.controller] (Controller Boot Thread) JBAS014774: Service status...

DDS TIMFMT. Is it possible to get hh:mm?

ibm-midrange
I have a time input field in a display record, and I want it to be in the format hh:mm. The available formats either have seconds or AM/PM afterwards. The following is what I would like to do, but it doesn't work since the format doesn't exist. A SRTTIME T...

Using a custom comparator for std::map on IBM i-Series

c++,ibm-midrange
I'm trying to use a std::map where the keys are c-style strings rather than std::strings but am having problems getting it to compile on an IBM iSeries targetting v7r1m0 I'd like to use c-style strings because using the Performance Explorer (PEX) it appears that the creation of lots of temporary...

Write records from one PF to another without READ operation or DOW loop or move operation.

copy,move,ibm-midrange,rpgle
I know how to copy records from one pf to another by reading one file in dow loop and writing into another file like below. Files are PF1 and PF2 having record format rec1 and rec2 respectively where each file have only one field named fld1 and @fld1 respectively- READ...

Creating views in as400 db2

db2,ibm-midrange
If I create a view in db2, say something like: select RNN(sometable) as rn, c1,c2,c3 from sometable order by rn desc fetch first 100 rows only This would essentially pull the last 100 rows from the table (I use RNN because there's nothing else to base that sort by, sadly....

DB2 for IBM i (AS400) query substr

sql,ibm-midrange,db2i
I have 48/50 g in a as400 database and I want to (query)substr before the "/" and before the space between 50 and g. Result I should get is 50 I'm searching for hours now and try and try but no luck. any help is welcome. Thanks!...

Iseries SQL DBU Flatten Row into Columns

sql,ibm-midrange,flatten
I have an iSeries server running V5R4. I need to flatten a row into columns. Here's an example of the data: Sequence Ordn Ordl Percentage 1 0140766 1 0 2 0140766 1 30 3 0140766 1 7 4 0140766 1 3 1 0140766 2 0 2 0140766 2 30 3...

SQL access to RPG OPM database files

ibm-midrange,db2400,rpg
I have to evaluate the legacy software used by a company my employer intends to buy. The software was originally programmed in RPG II (or III?) for IBM System/38 and been continuously expanded since. It now resides on a brand new i Series server with i5/OS and runtime environments for...

How to view Journal data of a Table Green screen DB2

db2,ibm,ibm-midrange,db2400
How do you view journal data from a specified table? I want to see what happended to a table in a specified time frame. I got info on the table by using the following command: DSPFD FILE(P6PRDBDB00/P6OIDPF) Got the journaled name and library but I dont know how to view...

Call procedure (long name) into variable (not free)

ibm-midrange,rpgle,rpg,icebreak
I'm trying to call a procedure and assign a variable with the output. My problem is that my procedure name is to long for the allowed space: This is my working source, is it posible to use move or movel and do the same? c eval pSitProp = json_parseString(wSitProp) ......

import/export CSV in IBM i (AS400) DB2

php,ibm-midrange,zend-server,db2400,jdedwards
I'm preparing for an upcoming integration project where I need to import/export some data to/from our ERP into the File System. We run JD Edwards World 9.2 on an IBM iSeries/AS400 V6R1 machine. The software we are integrating with, requires that we integrate using CSV files that are exported/imported on...

How to Call RPGIV program from java that returns more than one record

java,ibm,ibm-midrange,rpg,jtopen
I am calling a RPGIV program from java, the rpgiv program returnes multi record as an output parameter. I tried the following to return all the rows returned from rpgiv. // Define Output Data Structure AS400DataType[] outputData = { new AS400Text(20), // parentOperationsItemId; new AS400Text(10), // parentOperationsItemType; new AS400Text(10), //...

How to use CHGDTA in CL Programming ? (as400)

ibm-midrange
please note this is a task for school I'm trying to create a CL Program and this is the breakdown of the requirements I've gathered: CL program calls DFU program a menu with options will pop up - Enter CIF data - Edit CIF data - Exit When a user...

IBM i (AS/400) command works locally but not remotely

oledb,.net-2.0,ibm-midrange
I have a Windows service written in VB.NET 2.0 which connects to an IBM AS/400 server. Queries work fine, but when I try to do something like deleting a spool file, I get errors. For example: CPYSPLF FILE(PO630A) TOFILE(MPLCDATPAR/PO630APF) JOB(083064/ARUSER/POASYNCMON) SPLNBR(80) MBROPT(*REPLACE) Running this command with ExecuteNonQuery yields: CPF3342 -...

Is the available db2 express on IBM is similar as DB2 for IBM i (db2/as400) green screen uses?

php,db2,ibm-midrange,legacy,legacy-database
basically. I'm trying to develop a PHP app that requires an access to DB2 for IBM i (db2/as400) database. However, I'm not much familiar with DB2 for IBM i (db2/as400) environment, so I'm doing research. I'm planning to install db2 express i found here on my dev laptop hoping i...