Improve performance for SQL query reading file having milions of recods (AS/400 – SQLRPGLE program)
I am fetching only 14 records each time from a customer file(physical file) having milions of records. This file has 46 fields. Fetch is done in the follwoing manner, but it takes longer time - SELECT * FROM CUSTOMER A WHERE A.CLINM1 LIKE ‘B%’ AND (CLINM1 <> ” OR CLIPR1 <> ”) ORDER BY A.CLINM1 ASC, A.CLIPR1 ASC [...]
Answer Question
| March 17, 2012 5:23 AM
AS/400, AS400 - SQL Queries, SQLRPGLE
I am fetching only 14 records each time from a customer file(physical file) having milions of records. This file has 46 fields. Fetch is done in the follwoing manner, but it takes longer time - SELECT * FROM CUSTOMER A WHERE A.CLINM1 LIKE ‘B%’ AND (CLINM1 <> ” OR CLIPR1 <> ”) ORDER BY A.CLINM1 ASC, A.CLIPR1 ASC [...]
Answer Question
| March 17, 2012 5:23 AM
AS/400, AS400 - SQL Queries, SQLRPGLE
SQLRPGLE
C/EXEC SQL C+ SELECT DISTINCT(CTP),COUNT(*) INTO :$Ctp,:#Ctotl FROM CUSTPF C+ GROUP BY CTP C/END-EXEC CTP is CustomerType in Customer File Here actually i am getting 3 records as output. But i dont know how to write those 3 records into a printer file. Can anybody explain me how to write the output of a select [...]
Answer Question
| March 19, 2012 10:30 AM
AS/400, AS/400 output files, SQL commands, SQLRPGLE
C/EXEC SQL C+ SELECT DISTINCT(CTP),COUNT(*) INTO :$Ctp,:#Ctotl FROM CUSTPF C+ GROUP BY CTP C/END-EXEC CTP is CustomerType in Customer File Here actually i am getting 3 records as output. But i dont know how to write those 3 records into a printer file. Can anybody explain me how to write the output of a select [...]
Answer Question
| March 19, 2012 10:30 AM
AS/400, AS/400 output files, SQL commands, SQLRPGLE
AS400
Fstdt if e disk Dnam s 10a Ddept s 3a C/exec sql C+ SELECT NAME, BRANCH INTO :nam, :dept FROM stdt WHERE STDID < 10 C+ C/end-exec C seton lr Hai all ..! I have this code .. But i’m not getting the output.. how can i get [...]
Answer Question
| March 6, 2012 1:35 PM
SQLRPGLE
Fstdt if e disk Dnam s 10a Ddept s 3a C/exec sql C+ SELECT NAME, BRANCH INTO :nam, :dept FROM stdt WHERE STDID < 10 C+ C/end-exec C seton lr Hai all ..! I have this code .. But i’m not getting the output.. how can i get [...]
Answer Question
| March 6, 2012 1:35 PM
SQLRPGLE
AS400 – SQLRPGLE
Hi, We are moving Values to Database Fields using Embedded SQL Concept. In Embedded SQL, We can decide the Variable Names dynamically. Is it possible with Display File? why?
Answer Question
| March 17, 2012 9:49 AM
AS/400 Display File, AS400 - Embedded SQL, Dynamic SQL, Embedded SQL, SQLRPGLE
Hi, We are moving Values to Database Fields using Embedded SQL Concept. In Embedded SQL, We can decide the Variable Names dynamically. Is it possible with Display File? why?
Answer Question
| March 17, 2012 9:49 AM
AS/400 Display File, AS400 - Embedded SQL, Dynamic SQL, Embedded SQL, SQLRPGLE
Record Locking in SQLRPGLE
Hi All I have a SQLILE pgm which updates files through SQL statements. How to get the user id/job number which is locking the PF record through the program… if any data structure supports, whats the position of it… -Varun
Answer Question
| July 1, 2011 12:35 PM
AS/400 Records, Record lock, RPG Record Locks, SQLRPGLE
Hi All I have a SQLILE pgm which updates files through SQL statements. How to get the user id/job number which is locking the PF record through the program… if any data structure supports, whats the position of it… -Varun
Answer Question
| July 1, 2011 12:35 PM
AS/400 Records, Record lock, RPG Record Locks, SQLRPGLE
Commitment control in SQLRPGLE
Hi All As we know that using COMMIT keyword at file level, to acheive commitment control, however I would like to know how to acheive commitment control using SQLRPGLE with out defining the COMMIT.
Answer Question
| June 15, 2011 6:51 AM
AS/400, iSeries Commands, iSeries commitment control, RPG Commit, SQLRPGLE
Hi All As we know that using COMMIT keyword at file level, to acheive commitment control, however I would like to know how to acheive commitment control using SQLRPGLE with out defining the COMMIT.
Answer Question
| June 15, 2011 6:51 AM
AS/400, iSeries Commands, iSeries commitment control, RPG Commit, SQLRPGLE
Single commitment control in RPGLE program
I have a rpgle program that uses 3-4 sql statements to update and insert data in 3-4 tables . Now what i want is that when program runs successfully then only these changes in tables should reflect else roll back . So for this is there any way to provide a single commitment control for [...]
Answer Question
| May 17, 2011 4:18 PM
RPGLE Program, SQL statements, SQLRPGLE, V6R1
I have a rpgle program that uses 3-4 sql statements to update and insert data in 3-4 tables . Now what i want is that when program runs successfully then only these changes in tables should reflect else roll back . So for this is there any way to provide a single commitment control for [...]
Answer Question
| May 17, 2011 4:18 PM
RPGLE Program, SQL statements, SQLRPGLE, V6R1
Embedded SQL in RPGLE hdr, dtl fetch for subfile build
I am trying to join a header and a detail file and select all fields into data structures defined as external(filename). D dswtchdr E DS EXTNAME(wtchdr) D dswtcdet E DS EXTNAME(wtcdet) C $CreateCsr Begsr C/EXEC SQL C+ prepare S1 from :sqlstmt C/end-exec * C/EXEC SQL C+ declare C1 scroll cursor for S1 C/end-exec C EndSr [...]
Answer Question
| May 12, 2011 4:46 PM
AS/400 Subfiles, Embedded SQL, Header, SQL Server, SQLRPGLE
I am trying to join a header and a detail file and select all fields into data structures defined as external(filename). D dswtchdr E DS EXTNAME(wtchdr) D dswtcdet E DS EXTNAME(wtcdet) C $CreateCsr Begsr C/EXEC SQL C+ prepare S1 from :sqlstmt C/end-exec * C/EXEC SQL C+ declare C1 scroll cursor for S1 C/end-exec C EndSr [...]
Answer Question
| May 12, 2011 4:46 PM
AS/400 Subfiles, Embedded SQL, Header, SQL Server, SQLRPGLE
Embedded SQL in rpg for subfile build SQLCOD
My fetch next in sql is getting sqlcod = -000000181 and sqlstt = ’22007′. I looked this up. It says invalid date or time. Pointing to date data types in the file I am trying to read. The records contain two date fields and two time fields. How can I get around this? Thanks [...]
Answer Question
| May 10, 2011 6:07 PM
RPG, SQL, SQLCOD, SQLRPGLE
My fetch next in sql is getting sqlcod = -000000181 and sqlstt = ’22007′. I looked this up. It says invalid date or time. Pointing to date data types in the file I am trying to read. The records contain two date fields and two time fields. How can I get around this? Thanks [...]
Answer Question
| May 10, 2011 6:07 PM
RPG, SQL, SQLCOD, SQLRPGLE
How to handle record lock using embedded SQL?
I am using SQLRPGLE program,need to handle record lock in sql statement ..plz suggest?
Answer Question
| March 31, 2011 2:05 PM
AS/400 SQL, Embedded SQL, Embedded SQL in AS/400, SQL Server, SQLRPGLE
I am using SQLRPGLE program,need to handle record lock in sql statement ..plz suggest?
Answer Question
| March 31, 2011 2:05 PM
AS/400 SQL, Embedded SQL, Embedded SQL in AS/400, SQL Server, SQLRPGLE
CPF5032 and SQL RPG
How do I unlock the data file to avoid halt ? CL used to sort file and call an RPG that cleared a field (followed by further processing). When I switch to an SQLRPG to clear the field, I get CPF5032 in the next program. Yes, I tried the UNLOCK op code in the SQLRPG [...]
Answer Question
| March 31, 2011 7:34 PM
AS/400, AS/400 SQL, CPF5032, SQL Server, SQLRPGLE
How do I unlock the data file to avoid halt ? CL used to sort file and call an RPG that cleared a field (followed by further processing). When I switch to an SQLRPG to clear the field, I get CPF5032 in the next program. Yes, I tried the UNLOCK op code in the SQLRPG [...]
Answer Question
| March 31, 2011 7:34 PM
AS/400, AS/400 SQL, CPF5032, SQL Server, SQLRPGLE
SQLRPGLE Error in UPDATE Query
Hello, I have a SQL Statement in SQLRPGLE which Updates a 3 fiels in a file with 30 fields. SQL Statement is as below. UPDATE TABLE1 SET TXDATE = :SYSDATE, TXTIME = :SYSTIME, SETFLAG = :w_Set, MSG = :w_ERR, Comment = ‘ ‘ WHERE ID = :W_ID This works fine on one iSeries server and throws following error on [...]
Answer Question
| March 14, 2011 9:25 PM
AS/400, iSeries, RPG ILE, SQL, SQL statement, SQLRPGLE
Hello, I have a SQL Statement in SQLRPGLE which Updates a 3 fiels in a file with 30 fields. SQL Statement is as below. UPDATE TABLE1 SET TXDATE = :SYSDATE, TXTIME = :SYSTIME, SETFLAG = :w_Set, MSG = :w_ERR, Comment = ‘ ‘ WHERE ID = :W_ID This works fine on one iSeries server and throws following error on [...]
Answer Question
| March 14, 2011 9:25 PM
AS/400, iSeries, RPG ILE, SQL, SQL statement, SQLRPGLE
Embedded SQL number of days between dates
C/EXEC SQL C+ INSERT INTO SERIOVR60 C+ SELECT HTSERN, HTPART, HTQTY, HTSTKL, C+ HTSTS, HTADAT, C+ substr(HTPART, 1, 11) AS HTMODEL, C+ HTADAT – :CURDATE AS HTDAYS C+ FROM SERI C+ WHERE HTADAT < :TESTDATE and C+ HTADAT <> ’0001-01-01′ and C+ HTSTS = ‘A’ C+ ORDER BY HTPART C/END-EXEC This returns; Date = 2010-05-12 Days [...]
Answer Question
| November 13, 2010 5:07 PM
AS/400, Embedded SQL, SQL, SQLRPGLE
C/EXEC SQL C+ INSERT INTO SERIOVR60 C+ SELECT HTSERN, HTPART, HTQTY, HTSTKL, C+ HTSTS, HTADAT, C+ substr(HTPART, 1, 11) AS HTMODEL, C+ HTADAT – :CURDATE AS HTDAYS C+ FROM SERI C+ WHERE HTADAT < :TESTDATE and C+ HTADAT <> ’0001-01-01′ and C+ HTSTS = ‘A’ C+ ORDER BY HTPART C/END-EXEC This returns; Date = 2010-05-12 Days [...]
Answer Question
| November 13, 2010 5:07 PM
AS/400, Embedded SQL, SQL, SQLRPGLE
SQL RPGLE – How to override data set data structure at run time
I currently have an SQL RPGLE routine that receives a SELECT statement as a parameter and then fetches the data set into a data structure based on the file. This only works over a single file as the datastructure is created at compile time and has to be based on filea. I would like to [...]
Answer Question
| September 23, 2010 1:43 PM
iSeries, RPGLE, SELECT statement, SQL, SQL Select statement problem, SQLRPGLE
I currently have an SQL RPGLE routine that receives a SELECT statement as a parameter and then fetches the data set into a data structure based on the file. This only works over a single file as the datastructure is created at compile time and has to be based on filea. I would like to [...]
Answer Question
| September 23, 2010 1:43 PM
iSeries, RPGLE, SELECT statement, SQL, SQL Select statement problem, SQLRPGLE
What does this mean when SQLCOD = -206? I need to write 2 sets embedded SQL statement in my program.
what does this mean when SQLCOD = -206. I need to write 2 sets embedded SQL statement in my program. first one is one and I was able to fetch data but the 2nd one has error just right after I declare cursor. Here are the statements EVAL SQLSTMT2 = SELECT_H_2 + WHERE_H_2; + AND_H_A; [...]
Answer Question
| August 19, 2010 7:06 PM
iSeries SQL, SQL statements, SQLRPGLE, System i
what does this mean when SQLCOD = -206. I need to write 2 sets embedded SQL statement in my program. first one is one and I was able to fetch data but the 2nd one has error just right after I declare cursor. Here are the statements EVAL SQLSTMT2 = SELECT_H_2 + WHERE_H_2; + AND_H_A; [...]
Answer Question
| August 19, 2010 7:06 PM
iSeries SQL, SQL statements, SQLRPGLE, System i
Printing a PDF image from BLOB using SQLRPLGE
How to print a PDF image stored in BLOB from SQLRPGLE?
Answer Question
| July 13, 2010 6:54 AM
AS/400 printing, AS400 RPGLE, BLOB, SQLRPGLE
How to print a PDF image stored in BLOB from SQLRPGLE?
Answer Question
| July 13, 2010 6:54 AM
AS/400 printing, AS400 RPGLE, BLOB, SQLRPGLE
Trimming just one leading zero (if present) in SQLRPGLE
I have a SQLRPGLE program and have 6A character field. How to I trim just one leading zero(if present) Eg- if 001300 then I should insert 01300. Thanks!
Answer Question
| June 29, 2010 5:37 AM
iSeries, OS/400, SQLRPGLE
I have a SQLRPGLE program and have 6A character field. How to I trim just one leading zero(if present) Eg- if 001300 then I should insert 01300. Thanks!
Answer Question
| June 29, 2010 5:37 AM
iSeries, OS/400, SQLRPGLE
Activation Groups, RPGLE, and Open Files
Hello Everyone, We have a couple question regarding activation groups, RPGLE, and Open files. In our H specs for RPGLE and SQLRPGLE programs, some of our programs use dftactgrp(*yes), some use dftactgrp(*no), and some use dftactgrp(*no) actgrp(*caller). What are the differences? And, should all of our programs be something? 90% of our programs are using [...]
Answer Question
| June 16, 2010 7:28 PM
Activation Groups, iSeries, RPGLE, SQLRPGLE
Hello Everyone, We have a couple question regarding activation groups, RPGLE, and Open files. In our H specs for RPGLE and SQLRPGLE programs, some of our programs use dftactgrp(*yes), some use dftactgrp(*no), and some use dftactgrp(*no) actgrp(*caller). What are the differences? And, should all of our programs be something? 90% of our programs are using [...]
Answer Question
| June 16, 2010 7:28 PM
Activation Groups, iSeries, RPGLE, SQLRPGLE
SELECTION OF UNIQUE RECORDS FROM FILE USING SQLRPGILE CODE
I HAVE TO DO THE BELOW STEPS USING SQLRPG,PLZ HELP TO HANDLE THE UNIQUE RECORDS : 1. Delete records from Production LFPRIPMH/D file and those policy numbers are available in LFPS402S (unique policy number) 2. Insert into Production LFPRIPMH/D files from Before migration image (LFPRIPMH/D), where policy numbers are available in LFPS402S (unique policy number)
Answer Question
| June 7, 2010 11:25 AM
AS/400, RPGILE, SQLRPGLE
I HAVE TO DO THE BELOW STEPS USING SQLRPG,PLZ HELP TO HANDLE THE UNIQUE RECORDS : 1. Delete records from Production LFPRIPMH/D file and those policy numbers are available in LFPS402S (unique policy number) 2. Insert into Production LFPRIPMH/D files from Before migration image (LFPRIPMH/D), where policy numbers are available in LFPS402S (unique policy number)
Answer Question
| June 7, 2010 11:25 AM
AS/400, RPGILE, SQLRPGLE
Very simple SQLRPGLE not working
I must be missing something simple. Any ideas ? Here’s the code. C/exec sql C+ Update MFLLIB/ITMAST C+ Set IM_ITEMID = ‘ST150EML’ C+ Where IM_ITEMID = ‘ST150E ‘ C/end-exec C Eval *inlr = *on I simplified the update for test purposes. No halts but no update. Of course I can do the update via [...]
Answer Question
| May 6, 2010 8:21 PM
AS/400, SQLRPGLE
I must be missing something simple. Any ideas ? Here’s the code. C/exec sql C+ Update MFLLIB/ITMAST C+ Set IM_ITEMID = ‘ST150EML’ C+ Where IM_ITEMID = ‘ST150E ‘ C/end-exec C Eval *inlr = *on I simplified the update for test purposes. No halts but no update. Of course I can do the update via [...]
Answer Question
| May 6, 2010 8:21 PM
AS/400, SQLRPGLE
