View Full Version : retrieve data with view
Kurt Schrauwen
06-15-2010, 09:15 AM
Hey,
Is it a problem that the data retrieved using SQL on the RPG side is comming from VIEWS instead of phisycal or logical files ?
Thanks,
Kurt
robert.swanson
06-15-2010, 10:23 AM
Hi Kurt,
The ending data set should be the same no matter what index or file you specify, but you're typically going to see faster results on your SQL statement when the system invokes the newer SQL Query Engine (SQE) instead of the often less efficient Classic Query Engine (CQE). When you see temporary indexes being built to support your SQL statement, that is often the CQE at work. One of the most common causes of the CQE being used instead of the SQE is when you specify a DDS-defined logical file in the FROM clause. Another factor is that the mere existence of a select/omit logical file, even if you're not specifying it in your FROM clause, can trigger use of the CQE (there is a potential workaround to this, entails using the IGNORE_DERIVED_INDEX option).
So as a general rule, you should try to specify the physical file (or an explicit SQL view) in your FROM clause whenever possible.
There are other considerations in the SQE vs CQE arena worth looking into if you're really focused on improving your SQL performance. Here are a couple good references I found if you're interested:
IBM Support - Taking advantage of SQE (http://www-01.ibm.com/support/docview.wss?uid=nas11aebc93ba6cab05c8625739c006b65 ee)
MC Press Article - Maximize SQE Usage (http://www.mcpressonline.com/database/db2/maximize-sql-query-engine-sqe-usage-of-your-db2-web-query-reports.html)
(the MC Press article is actually about Web Query, but the same rules should apply to your RPG-based SQL statements)
-Rob
Kurt Schrauwen
06-16-2010, 05:23 AM
Hi,
Thanks for the explanation, this is the reason why I wanted to use a view instead of logical. But I can't get it working !
Is started from the example EXGRIDSQL2.
Everything is working well when I'm getting data from my physical file.
But when I change to my view there is no data.
I used sql "CREATE VIEW vA58 as ..." to make my view, the data set is the same as my physical file to test if it is working.
When I'm debugging the rpg program data is retrieved ans put into the variable I use to return.
vvOut_toJSON(vvOut:%addr(MAINrecords):count);
When I check variable "MAINrecords" it is filled in with data
but it does not get to my valence extjs window
Can someone give me a hint,
Kurt
sean.lanktree
06-16-2010, 08:37 AM
What is the value of vvOut.object? Is it set to the same object that your MAINrecords data structure is created from?
Kurt Schrauwen
06-16-2010, 08:51 AM
Hai,
Problem still exists.
I have a strait forward application.
When I debug the RPG application I have data in my MAINrecords ds but It wil not display the data in the grid in valence.
Yes the vvOut.object is the same as object as the MAINrecords data structure.
Is it possible to see the JSon data before it goes to de client ?
What am I missing here ?
Thanks,
Kurt
Kurt Schrauwen
06-23-2010, 09:12 AM
is it possible to see the JSon file on the valence ExtJs side ?
richard.milone
06-23-2010, 09:23 AM
You should use the Firefox browser with the Firebug debugger plugin to easily inspect data going back and forth between the browser and the RPG programs. Why don't you post up your RPG program code and the relevant front-end code (JsonStore at minimum) and we'll take a look at it.
Kurt Schrauwen
06-23-2010, 09:30 AM
Hai Richard,
Here is the code and I'm still debuging in firefox.
The data is not comming in my dataStoreMaster
Client Side code :
<html>
<head>
<!-- <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> -->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Copyright" content="Copyright ? 2009-2010 HERFURTH GROUP, All Rights Reserved">
<title>Credit Control History</title>
<!-- Extjs -->
<link rel="stylesheet" type="text/css" href="/extjs/resources/css/ext-all.css" />
<link rel="stylesheet" type="text/css" href="/vvresources/valence.css" />
<script type="text/javascript" src="/extjs/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="/extjs/ext-all.js"></script>
<script type="text/javascript" src="/vvresources/valence.js"></script>
<!-- CSS -->
<link rel="stylesheet" type="text/css" href="/vvresources/sds_css/icons.css">
<link rel="stylesheet" type="text/css" href="/vvresources/sds_css/sds_icons.css">
<link rel="stylesheet" type="text/css" href="/vvresources/sds_css/sds_style.css">
<link rel="stylesheet" type="text/css" href="/vvresources/sds_css/columnLock.css">
<link rel="stylesheet" type="text/css" href="/vvresources/sds_css/empty.css" id="theme">
<link rel="stylesheet" type="text/css" href="/vvresources/sds_css/Ext.ux.grid.RowActions.css">
<link rel="stylesheet" type="text/css" href="/vvresources/sds_css/rowactions.css">
<!-- JS -->
<script type="text/javascript" src="/vvresources/sds_scripts/tdgi.borderLayout.js"></script>
<script type="text/javascript" src="/vvresources/sds_scripts/PageSizePlugin.js"></script>
<script>
Ext.onReady(function() {
Ext.QuickTips.init();
Ext.form.Field.prototype.msgTarget = 'side';
function getFilterValue(){
//Ext.MessageBox.alert('Get Filter Value', 'Underconstruction');
var ls_where = '';
//var ls_where = "1 = 1";
//var ls_where = "BCSECL = '#H'";
//var ls_where = "BCKADR = 'A10565'";
//var ls_where = "BCKADR LIKE '%A1056%'";
return ls_where;
}
/* MASTER */
var dataStoreMaster = new Ext.data.JsonStore({
autoLoad: true,
url: 'vvcall.pgm',
remoteSort: true,
root: "OUPP",
totalProperty: "totalCount",
fields: ['OUKSIT','OUKSRT','OUKREO','OUKDEL','OUKVWK','OUKL CK','OUOUPP','OUWEEK','OULEVL']
});
var colModelMaster = new Ext.grid.ColumnModel([
//new Ext.grid.RowNumberer(),
{header: "<b>OUKSIT</b>", dataIndex:'OUKSIT', hidden:false, width:75, sortable:true},
{header: "<b>OUKSRT</b>", dataIndex:'OUKSRT', width:75},
{header: "<b>OUKREO</b>", dataIndex:'OUKREO', width:75},
{header: "<b>OUKDEL</b>", dataIndex:'OUKDEL', width:75},
{header: "<b>OUKVWK</b>", dataIndex:'OUKVWK', width:75},
{header: "<b>OUKLCK</b>", dataIndex:'OUKLCK', width:75},
{header: "<b>OUOUPP</b>", dataIndex:'OUOUPP', width:75},
{header: "<b>OUWEEK</b>", dataIndex:'OUWEEK', width:75},
{header: "<b>OULEVL</b>", dataIndex:'OULEVL', width:75}
]);
// grid
var gridMaster = new Ext.grid.GridPanel({
id:'gridMaster',
region: 'center',
autoScroll: true,
trackMouseOver: true,
loadMask: true,
iconCls: 'icon-grid',
title: 'CREDIT CONTROL : History file',
stripeRows: true,
store: dataStoreMaster,
columnLines: true,
enableColumnMove: false,
cm: colModelMaster,
bbar: new Ext.PagingToolbar({
pageSize: 25,
store: dataStoreMaster,
displayInfo: true,
displayMsg: 'Displaying record(s) {0} - {1} of {2}',
emptyMsg: "No records to display",
plugins: [new Ext.ux.PageSizePlugin({editable:false, forceSelection:true})]
}),
selModel: new Ext.grid.RowSelectionModel({singleSelect:true})
});
// viewport
var viewport = new Ext.Viewport({
layout:'tdgi_border',
items: [gridMaster]
});
// listener: set grid call parameters
dataStoreMaster.on("beforeload", function() {
dataStoreMaster.baseParams = {
pgm: 'VAL_CCHIST',
action: 'getCCHistList',
search: getFilterValue()
};
});
// listener: set grid call parameters
dataStoreMaster.on("load", function() {
//Ext.getCmp('search').focus();
});
});
</script>
</head>
<body>
</body>
</html>
RPG code :
*************** Beginning of data ************************************************** **********************
0001.00 h bnddir('VVBNDDIR') 090701
0002.00 /copy qcpylesrc,vvHspec 090701
0003.00 100223
0004.00 ** -------------------------------------------------------------- 080131
0005.00 ** Object ID : VAL_CCHIST 100623
0006.00 ** Description : Credit Control History 100623
0007.00 ** -------------------------------------------------------------- 080131
0008.00 ** History : KS : 23/06/2010 : New 100623
0009.00 ** -------------------------------------------------------------- 080131
0010.00 100223
0011.00 ** internal variables 080516
0012.00 d action s 20a 080710
0013.00 d stmt s 500a varying 080710
0014.00 d count s 3 0 080710
0015.00 d start s 7 0 080710
0016.00 d limit s 3 0 080710
0017.00 d search s 50a 100614
0018.00 d sort s 10a 080710
0019.00 d sortDir s 5a 080710
0020.00 d sq_Num s 15 5 080710
0021.00 d exportToCSV s n 100303
0022.00 d columnArray s 10a dim(20) 100304
0023.00 d colCnt s 2 0 100304
0024.00 d ii s 2 0 100304
0025.00 d RECORDS c const(1) 080710
0026.00 d VCOUNT c const(2) 080711
0027.00 d sq_Data e ds extname(OUPP) 100623
0028.00 d MAINrecords e ds extname(OUPP)qualified 100623
0029.00 d dim(500) 080518
0030.00 080516
0031.00 ** internal procedures 080518
0032.00 d GetPostParms pr 080518
0033.00 d GetData pr 080518
0034.00 d GetCount pr 10a varying 080518
0035.00 d SqlGetStmt pr 080518
0036.00 d mode 1 0 const 080518
0037.00 d SqlSetRecord pr 080518
0038.00 d SqlPrepStmt pr n 080518
0039.00 d SqlOpenCursor pr 080518
0040.00 d SqlGetRecord pr n 080518
0041.00 d mode 1 0 const 080516
0042.00 d SqlClsCursor pr 080518
0043.00 d getCustSupp pr 100607
0044.00 d getCustSuppCSV pr 100607
0045.00 100303
0046.00 /include qcpylesrc,vvDspec 090724
0047.00 100223
0048.00 **--------------------------------------------------------------- 080516
0049.00 ** program start 080516
0050.00 **--------------------------------------------------------------- 080516
0051.00 /free 080516
0052.00 action = vvIn_char('action'); 100623
0053.00 //action = 'getCCHistList'; 100623
0054.00 100303
0055.00 select; 100303
0056.00 when action = 'getCCHistList'; 100623
0057.00 getCustSupp(); 100607
0058.00 when action = 'getCustSuppListCSV'; 100607
0059.00 exportToCSV=(action='getCustSuppListCSV'); 100607
0060.00 getCustSuppCSV(); 100607
0061.00 endsl; 100303
0062.00 100303
0063.00 *inlr=TRUE; 080516
0064.00 /end-free 080516
0065.00 100223
0066.00 **--------------------------------------------------------------- 080516
0067.00 ** The following SQL option allows our where constraints to 090925
0068.00 ** ignore case... 080518
0069.00 ** 080518
0070.00 c *inzsr begsr 080518
0071.00 c/exec sql 080518
0072.00 c+ set option srtseq=*langidshr 081014
0073.00 c/end-exec 080518
0074.00 c endsr 080518
0075.00 100303
0076.00 **--------------------------------------------------------------- 080518
0077.00 ** Start CSV export 100303
0078.00 **--------------------------------------------------------------- 100303
0079.00 p getCustSuppCSV b 100607
0080.00 100303
0081.00 d getCustSuppCSV pi 100607
0082.00 100303
0083.00 /free 100303
0084.00 GetPostParms(); 100303
0085.00 GetData(); 100303
0086.00 100303
0087.00 vvOut.embed=TOTALCOUNT+GetCount(); 100303
0088.00 vvOut.object='OUPP'; 100623
0089.00 if exportToCSV; 100303
0090.00 vvOut.download='Y'; 100303
0091.00 vvOut.file ='excsvdload'+%char(%timestamp())+'.csv'; 100303
0092.00 //vvOut_toCSV(vvOut:%addr(CSVrecordsArry):count); 100303
0093.00 vvOut_toCSV(vvOut:%addr(MAINrecords):count); 100615
0094.00 else; 100303
0095.00 //vvOut_toJSON(vvOut:%addr(CSVrecordsArry):count); 100303
0096.00 vvOut_toJSON(vvOut:%addr(MAINrecords):count); 100615
0097.00 endif; 100303
0098.00 /end-free 100303
0099.00 p e 100303
0100.00 100303
0101.00 **--------------------------------------------------------------- 100303
0102.00 100303
0103.00 p getCustSupp b 100607
0104.00 080516
0105.00 d getCustSupp pi 100607
0106.00 080516
0107.00 /free 080516
0108.00 GetPostParms(); 080518
0109.00 GetData(); 080516
0110.00 vvOut.embed=TOTALCOUNT+GetCount(); 090701
0111.00 vvOut.object='OUPP'; 100623
0112.00 vvOut_toJSON(vvOut:%addr(MAINrecords):count); 100615
0113.00 /end-free 080516
0114.00 p e 080516
0115.00 100607
0116.00 **--------------------------------------------------------------- 080518
0117.00 100607
0118.00 p GetPostParms b 080518
0119.00 080518
0120.00 d GetPostParms pi 080518
0121.00 080518
0122.00 d DEFAULTROWS c const(25) 090701
0123.00 /free 080518
0124.00 start=vvIn_num('start'); 090701
0125.00 limit=vvIn_num('limit'); 090701
0126.00 if limit=0; 090507
0127.00 limit=DEFAULTROWS; 090507
0128.00 endif; 090507
0129.00 search = vvIn_char('search'); 100623
0130.00 //search = '1 = 1'; 100623
0131.00 sort =vvIn_char('sort'); 090701
0132.00 sortDir=vvIn_char('dir'); 090701
0133.00 /end-free 080518
0134.00 p e 080518
0135.00 100607
0136.00 **-------------------------------------------- 080516
0137.00 100607
0138.00 p GetData b 080518
0139.00 080516
0140.00 d GetData pi 080518
0141.00 080516
0142.00 /free 080516
0143.00 SqlClsCursor(); 080518
0144.00 SqlGetStmt(RECORDS); 080518
0145.00 if SqlPrepStmt(); 080518
0146.00 SqlOpenCursor(); 080527
0147.00 if start<>0; 080710
0148.00 SqlSetRecord(); 080518
0149.00 endif; 080516
0150.00 dow SqlGetRecord(RECORDS) and count<limit; 080710
0151.00 count+=1; 080710
0152.00 MAINrecords(count)=sq_Data; 100615
0153.00 enddo; 080516
0154.00 endif; 080516
0155.00 return; 080516
0156.00 /end-free 080516
0157.00 p e 080516
0158.00 100607
0159.00 **-------------------------------------------- 080516
0160.00 100607
0161.00 p GetCount b 080518
0162.00 080516
0163.00 d GetCount pi 10a varying 080518
0164.00 080516
0165.00 /free 080516
0166.00 SqlClsCursor(); 080518
0167.00 SqlGetStmt(VCOUNT); 080711
0168.00 if SqlPrepStmt(); 080518
0169.00 SqlOpenCursor(); 080518
0170.00 SqlGetRecord(VCOUNT); 080711
0171.00 endif; 080516
0172.00 return %char(sq_Num); 080710
0173.00 /end-free 080516
0174.00 p e 080516
0175.00 100607
0176.00 **-------------------------------------------- 080516
0177.00 100607
0178.00 p SqlGetStmt b 080518
0179.00 080516
0180.00 d SqlGetStmt pi 080518
0181.00 d inMode 1 0 const 080518
0182.00 080516
0183.00 d numToFetch s 10i 0 080710
0184.00 100607
0185.00 /free 080516
0186.00 select; 100304
0187.00 when action = 'getCCHistList'; 100623
0188.00 if inMode=RECORDS; 100304
0189.00 numToFetch=limit+start; 100304
0190.00 stmt='select * from OUPP'; 100623
0191.00 elseif inMode=VCOUNT; 100304
0192.00 stmt='select count(*) from OUPP'; 100623
0193.00 endif; 100304
0194.00 when action = 'getCustSuppListCSV'; 100607
0195.00 // retrieve the column array... 100304
0196.00 colCnt = vvIn_array('columns' 100304
0197.00 :%addr(columnArray) 100304
0198.00 :%size(columnArray:*all) 100304
0199.00 :%size(columnArray)); 100304
0200.00 if inMode=RECORDS; 100304
0201.00 stmt = 'select'; 100304
0202.00 for ii = 1 to colCnt; 100304
0203.00 stmt+=' '+%trim(columnArray(ii)); 100304
0204.00 if ii < colCnt; 100304
0205.00 stmt+=COMMA; 100304
0206.00 endif; 100304
0207.00 endfor; 100304
0208.00 stmt+=' from OUPP'; 100623
0209.00 100304
0210.00 numToFetch=limit+start; 100304
0211.00 //stmt='select BCSECL,BCKADR from OUPP'; 100623
0212.00 elseif inMode=VCOUNT; 100304
0213.00 stmt='select count(*) from OUPP'; 100623
0214.00 endif; 100304
0215.00 endsl; 100304
0216.00 100607
0217.00 if search<>*blanks; 080710
0218.00 stmt+=' where '+%trim(search); 100615
0219.00 endif; 080517
0220.00 100607
0221.00 if inMode=RECORDS; 080518
0222.00 if sort<>*blanks; 080710
0223.00 stmt+=' order by '+%trim(sort); 080710
0224.00 else; 080518
0225.00 stmt+=' order by OUOUPP'; 100623
0226.00 endif; 080518
0227.00 if sortDir<>*blanks; 080710
0228.00 stmt+=' '+%trim(sortDir); 080710
0229.00 endif; 080518
0230.00 stmt+=' fetch first '+%char(numToFetch)+' rows only'; 080710
0231.00 endif; 080518
0232.00 return; 080516
0233.00 /end-free 080516
0234.00 p e 080516
0235.00 100607
0236.00 **-------------------------------------------- 080516
0237.00 100607
0238.00 p SqlPrepStmt b 080518
0239.00 080411
0240.00 d SqlPrepStmt pi n 080518
0241.00 080411
0242.00 d STOP c const('02000') 080411
0243.00 080411
0244.00 c/exec sql prepare alldata from :stmt 080710
0245.00 c/end-exec 080411
0246.00 c if sqlstt<>STOP 080411
0247.00 c return TRUE 080411
0248.00 c else 080411
0249.00 c return FALSE 080411
0250.00 c endif 080411
0251.00 p e 080411
0252.00 100607
0253.00 **-------------------------------------------- 080411
0254.00 100607
0255.00 p SqlOpenCursor b 080518
0256.00 080411
0257.00 d SqlOpenCursor pi 080518
0258.00 c/exec sql 080411
0259.00 c+ declare csr001 scroll cursor for alldata 080516
0260.00 c/end-exec 080411
0261.00 c/exec sql open csr001 080527
0262.00 c/end-exec 080411
0263.00 c return 080411
0264.00 p e 080411
0265.00 100607
0266.00 **-------------------------------------------- 080411
0267.00 100607
0268.00 p SqlGetRecord b 080518
0269.00 080411
0270.00 d SqlGetRecord pi n 080518
0271.00 d inMode 1 0 const 080516
0272.00 080411
0273.00 d GOOD c const(0) 080411
0274.00 c select 080516
0275.00 c when inMode=RECORDS 080518
0276.00 c/exec sql fetch next from csr001 into :sq_Data 080710
0277.00 c/end-exec 080411
0278.00 c when inMode=VCOUNT 080711
0279.00 c/exec sql fetch next from csr001 into :sq_Num 080710
0280.00 c/end-exec 080516
0281.00 c endsl 080516
0282.00 c if sqlcod=GOOD 080411
0283.00 c return *on 080411
0284.00 c else 080411
0285.00 c return *off 080411
0286.00 c endif 080411
0287.00 p e 080411
0288.00 **-------------------------------------------- 080516
0289.00 p SqlSetRecord b 080518
0290.00 080516
0291.00 d SqlSetRecord pi 080518
0292.00 080516
0293.00 c/exec sql fetch relative :start from csr001 into :sq_data 080710
0294.00 c/end-exec 080516
0295.00 p e 080516
0296.00 **-------------------------------------------- 080411
0297.00 p SqlClsCursor b 080518
0298.00 080411
0299.00 d SqlClsCursor pi 080518
0300.00 c/exec sql close csr001 080411
0301.00 c/end-exec 080411
0302.00 p e 080411
****************** End of data ************************************************** *************************
Thanks,
Kurt
richard.milone
06-23-2010, 09:49 AM
I don't see anything obviously wrong. Are you using Firebug? At this point we would really need to see the post and response data from Firebug. If you have that, please post it up.
Kurt Schrauwen
06-23-2010, 09:57 AM
Yes I'm using firebug.
Where can I find the post in firebug ?
Do you mean the response from the dataStoreMaster ?
Sorry for my ignorance about firebug,
Kurt
richard.milone
06-23-2010, 10:03 AM
Firebug's "Console" tab records every Ajax communication. When you expand a communication record you will see Post and Response tabs. The Post tab records all the variables that you sent to the RPG program. The Response tab shows everything that comes back. Play around with that for a while and make sure you understand how it works. Using the Firebug Console tab is an essential part of the debugging process.
Kurt Schrauwen
06-24-2010, 04:14 AM
I have found the problem.
I did not fill in the "Authorized System i Programs for this Option" correctly, it was using the wrong RPG program.
Again something I never gone forget !
Thanks,
Kurt
Powered by vBulletin® Version 4.1.11 Copyright © 2012 vBulletin Solutions, Inc. All rights reserved.