June 29, 2020
Two new User Defined Functions were added for use on Date fields. YYYYMM will create a 6,0 Decimal field and YYMM will create a 4,0 Decimal field.
CVTDATE and CVTTIME can be used with *LOCALSYS/*SERVER views.
Support added for the MariaDB driver. This will be added during install.
Enabled support for JD Edwards EnterpriseOne 9.2 native long password support. This requires E1 Tools 18.104.22.168 or higher.
Versioning of the same object was limited to 1000 times due to the file naming convention and gave an MCH1201 ‘Receiver value too small’ error. The file will now have date/time suffix therefore removing the 1000 limit.
Custom JDE table is missing UDC fields and data dictionary formatting in Viewpoint or browser.
When a numeric JDE field that supported variable precision (via the data dictionary) was used in a GROUP BY, an SQL error occurred.
Using the Viewpoint Universal Driver for JDE and ADS connections to build a view will allow file list over libraries have more 999 files.
Scripts running commands with email requests that encountered EML0070 ‘Spool file attributes not eligible for selection’ will end abnormally so it is apparent that the email was not send.
A previous change from version 11.08 related to EXPR variables used in Scripts has been reverted back so that trailing blanks on a character value will be trimmed.
Script with EXPR type variables passing blank value does not build quotes properly.
From a script a REPORT command successfully produces a spooled file, but fails to email the file due to EML0070 did not cause the script to end abnormally despite an Escape 40 message. The script will now end abnormally.
EXECUTE PCFMT(*XLSX) with RCDFMT created excel file that is corrupt.
EXECUTE command can write the view results to another IBMi by specifying the appropriate TOSERVER and TOTABLE using a runtime prompted view failed.
EXECUTE to XLSX file using a *LOCALSYS/*SERVER view gave MCH0603 unmonitored by QRYO07 in rare instances when the offset for the column headings equaled 48.
QSTRING variable values without default values now truncate after first 3 positions
Sequel Install may fail if CPI432A was previously journaled.
Repeated running of the same view using *LOCALSYS *SEQUEL may result in MCH1210 ‘Receiver value too small to hold result’.
ADS Server Options set to only return libraries in the user's library list from View Builder, the second attempt to create a view did not show the list of files.
- VPTRMTCMD—A replacement for RUNRMTCMD to run Client Reports, Client Tables from SWI and from scripts has been created. VPTRMTCMD is also supported on Windows 10.
- CURDATE() and CURTIME() functions added to SCSERVER10/SQLPARMS so they will appear in the Viewpoint Expression Editor list of functions.
- EXECUTE to XLSX displays Function check and MCH1210 from QRYO07 signaled to SQLEXEC for some data.
- In the Expression Editor for a view set to Syntax *SERVER, the FLOAT function was not in the list.
- EXECUTE over SQLServer database erroneously gives error QRY2301 Identifier 'NAME' preceding '(VARCHAR)2' is used incorrectly.
- No records selected caused by incorrect quoting on SETVAR variable values for QSTRING variables.
- Client Report gives error "Column $CNM04 or expression in SELECT list not valid" when using a the Viewpoint Universal Driver (ADS or JDE connection) when the GROUP BY clause had an expression.
- Using *LOCALSYS and *SEQUEL, the SQL translation was not ordering files correctly when using a partial outer join with several files and a subquery.
- A Localsys/*Server view with parenthesis in the column heading truncates the heading after the first ) when results saved as physical file.
- From a Script, MONMSGs following RUNCMDs are being triggered when the command ends in error.
- While in Viewpoint Designer, JDE driver not dynamically creating calculation when length of a field is changed by user (host fix).
- Using EXECUTE to create xlsx output, the sheet name had a random V00000xxxx name instead of Sheet1.
- EXECUTE to send an email in PDF no longer ignores the SETDFT setting of ‘Send if no data’ when no records are returned.
- The VFYREPO command can cause a CPF1ED7 message if the current directory is not '/'.
- A new Verify Repository command has been added to be used by an Administrator that verifies the integrity of the repository, by performing cross-checks between the repository files and a list of specified libraries. If any orphaned files or user spaces are found, reports are produced identifying the orphaned objects.
- Java connections for remote databases now allow custom values for SystemDefault.Properties & Environment Variables.
- Commitment control should not be automatically started for *LOCALSYS and *LOCAL views.
- CVTSQTOIFS will create repository objects with lower case extensions (i.e. vptview).
- Using RUNSCRIPT with SETVAR if last variable is an EXPR the first character of the preceeding variable value is truncated
- DSPVIEWD fails with the error MCH0601 Space offset message when the source being written exceeds 9999 lines.
- Error MCH3402 Tried to refer to all or part of an object that no longer exists when running multiple scripts from a CL program in batch.
- RUNSCRIPT passing an empty string on the SETVAR gives RNX0100 length or start position is out of range for the string operation.
- UPDATE and INSERT fail for users with *STRICT authority with MCH0801 Argument associated with external or internal parameter not passed.
- Results from a CTE were not displaying Column headings defined from the inner SELECT correctly.
- Script passing EXPR variables fails in some instances with CPD0020 Character '0' not valid following string ''sequel/ex'. (Caused in Mod08).
- EXECUTE fails to signal error if pre existing target IFS file is checked out (CHKOUT)
- EXECUTE using ALIAS keyword gives error CPF327D alternative name specified more than once.
- Running *LOCALSYS views, , if MSG(*NO) is specified, query messages should be suppressed.
- Auditing was not showing use of EXECUTE command.
- Host Install will now add new connection types to MNTHOSTF.
- ADS security now supports fields with long names (greater than 10)
- GETLAST and GETLASTLOC functions will now support a blank delimiter.
- The Viewpoint Universal Driver with a JDE connection was not observing the decimal positions when the field was used on the WHERE or HAVING clause.
- Script Designer/Warehouse Builder did not allow command strings over 5,000 positions gave MCH1210 ‘receiver value too small to hold result’ error.
- Scripts/Warehouse Builder Definitions with commands using UNION gave Java exception error in EXPORTSQL.
- EXECUTE to create of XLSX output produces sets of folder in /tmp that are not removed when errors happen. The process can fail due to IFS1007 user profile space constraints, QRY7018 spreadsheet size constraint, MCH1201 Scalar data cannot be changed to type required, CPF9838 User profile storage limit exceeded, MCH3603 Pointer addresses object type not valid for this operation and probably others. The excess folders will no longer remain in the event of an error.
- EXECUTE gives CPF2479 ‘Call stack entry not found’ when the user has *STRICT authority, the query does not contain any of the fields that the user is prohibited from using, and the SQL is invalid.
- Running BLDJDELF multiple times in a job results in ‘CPF4205 Shared open not allowed.’ User had to start a new job in order to run the command again.
- Directly running the CVTSQTOIFS command does not copy any extra .rpt objects into the repository for CRO reports. It also does not copy extra .json files out there for client tables.
- CVTSQTOIFS will not attempt to migrate dashboard objects to Repository mode.
- RUNSCRIPT from a command line with Variables defined longer than 30 are losing the first line of prompt when running a script.
- EXECUTE to XLSX format will now use the value from the RCDFMT parameter for the sheet name in the spreadsheet.
- CVTQRY fails with error QRY2301 on CVTANSI when the view contains fields that are SQL keywords like PLAN and TYPE and using SERER(*LOCALSYS) SYNTAX(*SERVER).
- From Mod 8 to Mod 10, running scripts with an EXPR variable type whose length was less than the maximum length, would send informational message of CPI4314 ‘Comparison of selection operands may never be equal’. This was due to a change where we were truncating trailing blanks on an expression variable value. It has been changed to pass the value with the padded blanks.
- Running a DYNSQL request to delete records from a file could result in CPF4278 ‘Query definition template not valid’. Then all subsequent DYNSQL requests, even SELECT statements that worked previously in the job now fail with QRY9995 java.lang.ArrayIndexOutOfBoundsException: Array index out of range: 0.
- Email sent using BCHEXECUTE did not resolve variables in view Description.
- XLSX file would show error -We found a problem with some content in file name.xlsx. Do you want us to try to recover as much as we can- when opening if there were special characters GT LT in the text attribute on a View result.
- Viewpoint Script Designer gave error CPD0074 “Value 'EXECUTE SQ' for COMMAND exceeds 5000 characters” when trying to run a script/warehouse builder definition with a long command line.
- Viewpoint Script Designer gave error "An error occurred initializing the script: Index and length must refer to a location within the string. ‘Parameter name:’ Length" trying to edit a script/warehouse builder definition with a long command string that has been saved.
- A Host Table when user had *SEQUEL authority would give QRY2303 ‘operator follows proper SQL statement from QRYP01’ or if *STRICT would give CPF9898 ‘Data Access restriction instead of QRY9999’.
- From DSNSCRIPT on the green screen, previously the script could only be run once using F19 if changes were made. Now the script can be run multiple times.
- Exporting view output in Viewpoint to an Excel file when using SERVER(*LOCALSYS) SYNTAX(*SERVER) and numeric fields that have an edit word or an edits code that places non-numeric characters in the field (such as / or -), causes the export to error out.
- Passing complex variable values fail with QRY3004 -string never ended or longer than 256 error- in a script or CL program. For instance: SEQUEL/DISPLAY VIEW(library/viewname) SETVAR((&PARM -CSTTE =IL))
- REPORT command to PDF to the IFS, should not have added a ^ character prior to the & character when an ampersand was use in the file name.
- BCHPRINT command does not submit for prompted views.
- EXECUTE to produce XLSX output has skipped the shared strings processing due to performance reasons since R11M09. That meant we did not generate the /x1/sharedStrings.xml portion of the xlsx document. But the [Content_Types].xml we zipped into the xlsx document still made a reference to the missing /x1/sharedStrings.xml. This did not cause Excel to complain when opening the document, but other products such as the ClearSwift email filter can treat the xlsx as defective.
- The WEEK function over a JDE date field mistakenly started the week on Monday rather than Sunday.
- Runtime SQL conversion using SERVER(*LOCALSYS) SYNTAX(*SEQUEL) cause error RNX0301 ‘Java exception received when calling java method’ using a sub-query.
- Presence of an ALIAS name in a view causes the field names to be displayed rather than column headings.
- Using *LOCALYS/*SEQUEL the converted SQL may have been written to change the ordering of list of join field to try to map with the order of the files in the FROM clause. The should allow a more accurate rebuilding of the FROM/JOIN clause.
- A migrated ShowCase DBQ that uses the ADS driver and ShowCase aliases. The view displays results in both ViewPoint and SWS-Repository Mode. However, when the view is opened in ViewPoint Design, we get an error when going to the Files & Fields tab: Couldn't find field COLUMN0001.
- Warehouse Builder scripts that were migrated didn’t run in Repository Mode. The .vptscript objects were created with CCSID 5348 and should have been created with 1252.
- Connections to Oracle 12 databases are now supported.
- The method to retrieve a list of files by the View Builder in Viewpoint was improved to increase performance.
- Presence of an ALIAS name in a view causes the field names to be displayed rather than column headings for derived fields.
- REPORT command using back slashes (\) in the IFS path was not creating the PC file for TXT, RTF or HTML.
- Running a script from SWI with a variable Default value *USER required *NOPROMPT in uppercase in order for user id to substitute.
- RUNSCRIPT could fail when non-EXPR type variables were followed by EXPR type variables on the SETVAR.
- RUNSCRIPT fails when some, but not all variable values are passed and fails to use the default value on the others.
- In host mode, editing an existing object and then saving it to repository will save the user space object in the library of the original object. Previously it saved to the repository library.
- In Repository Mode:
- Datasources names now support mixed-case.
- A Script View run from Viewpoint failed to release file locks when script view results window is closed.
- In Viewpoint repository mode, when a JDE server is prompted for Environment and Role, the server is disconnected to ensure the new credentials are used. If a user designed a view using a JDE datasource and immediately designed any other JDE view which used the same server as the original view a ‘file not found’ error was sent.
- With versioning off, the SAVE AS option, even though you specify the same object name, you will get multiple versioned objects of the *USRSPC object.
- In views using the Viewpoint Universal Driver for JDE and ADS connections when edit code or edit word is not explicitly stated on the SELECT clause will assign codes using the following rules:
- Numeric fields with no decimal places will be given edit code M, which is no comma separator, no decimal places and a minus sign for negative numbers.
- Numeric fields with decimal places will be given edit code J, which is comma separated, decimal places and a minus sign for negative numbers.
- The installation will create the SEQUELEX objects in the Repository.
- DSPSCRIPTD will now include Script View attributes in the command.
- EXECUTE with RECIPIENT in PDF format truncated subject line at 50 characters.
- From View Designer or View Builder, iASP libraries and files are not listed in Add Database dialog with an Advanced Database Security connection using the Viewpoint Universal Driver.
- Creating XLSX output ends with a Scalar error after a certain amount of data is produced.
- A JDE connection using the Viewpoint Universal Driver with a concatenation expression like, concat(trim(field1),concat(',',field2)) yielded results like so: àÁ>ÎÁÊä.
- CVTTIME and TIMESTAMP expressions only recognized as General format when saved to XLS.
- Connections made with the Viewpoint Universal Driver with JDE or ADS connections, would have column headings that ran together and on a single line.
- Connections using the Viewpoint Universal Driver for JDE or ADS connections will now allow expression in mixed case, not merely all upper case.
- MNTHOSTF copies both Driver and Connection strings from corresponding type template.
- The Viewpoint Universal Driver used for JDE and ADS connection with an expression using the CAST function gave error ‘QRY9995 Token - was not valid. Valid tokens.’
- Changes were made to enable ViewPoint to offer better support of Advanced Database Security when libraries and files reside on an independent auxiliary storage pool (iAsp).
- All XLSX files will be created as UTF-8. UTF-8 supports conversions from both single-byte and double-byte character sets. It also creates XLSX files that can be read on Apple devices. However, the Apple device must have Excel installed. Many iphones may not have Excel by default but information is available here:
- XLXS output created from the EXECUTE command has been enhanced to support use in a view as an Excel datasource.
- View description will be carried through to the File Properties Title when saving results as an XLSX file
- Encrypt the password for the remote databases within the MNTHOSTF entry.
- Scripts viewed from Viewpoint will now log script commands in the joblog.
- Request run from Automate Schedule that produce no records (which can be reasonable at times) were erroneously emailing the versioned copy of the results.
- When using a remote connection to an IBM i, Viewpoint did not use the SYSTEM_COLUMN_NAME which could cause fields to appear as RENAMED00x instead of the actual field name.
- Connection to a remote iSeries which is configured with system value QPWDLVL 0 (i.e. 10 characters) fails with MCH3401 ‘Cannot resolve to object GETSQLIB’ and RNQ020 ‘The call to JNIAAllocEn ended in error’.
- When using the Viewpoint Universal Driver (i.e. JDE and ADS connections) it did not return the 10-character field name when there was an ALIAS and RENAMED00x would be used.
- DBCS data printed from view with overflow to SQLPRTx files causes random strike through.
- Error ‘User does not match with the protocol specified in URL’ when connecting to a JD Edwards EnterpriseOne datasources due to conflicts with java versions.
- .VPT Shortcuts are unnecessarily created in repository folder when saving Sequel objects from Viewpoint this causes issues for users without proper authority.
- Scripts and Script Views not passing correct quoted values in SETVAR when run from Viewpoint Explorer.
- A script with a REPORT command to run a CRO report from Viewpoint gave error ‘Stream file error – no such path or directory’. Problem was introduced in Mod 7.
- View uses the default value for a variable rather than the value passed in SETVAR in unique circumstances (view had a variable with a *ALL default value, an integrity test of a DBList or values list, and the presence of a *NOPROMPT variable).
- Viewpoint objects could be created an iAsp and if existing iAsp objects were designed and saved, the view is corrupted and unusable and caused error CPFA0A9 ‘Object not found. Object is QSYS.LIB/yourlib.LIB’. Introduced in Mod 7.
- EXECUTE to an outfile with KEYFLDCNT(*ALL) using a Viewpoint Universal Driver connection (i.e. JDE or *ADS) when the ORDER BY uses numbers, not names caused error CPF3206 ‘Key field xxxx not found in VIEWFMT’.
- SERVER(*LOCALSYS) SYNTAX(*SEQUEL) views not displaying column headings correctly. Alt names were used instead of column headings, and *BLANK did not blank out the column heading.
- Repository mode now offers an authentication model that allows for:
- Users don’t require an IBM i profile
- JDE prompting for Environment and Role
- An interface that is based on the folder structure of the Repository
- Use of long object names in both Viewpoint and SWI from the Repository
- PostgreSQL database support added.
The TOSTMF path on the EXECUTE command size limit was increased from 128 characters to 2,000 characters.
- Runtime SQL conversion for SERVER(*LOCALSYS) SYNTAX(*SEQUEL) displays the error QRY2232 ‘Cannot translate SQL from *SEQUEL syntax to standard SQL’ when field was named MOD.
- After installing R11M05, processes writing to the QNTC folder no longer created a file.
- When user defaults are set to Versioning and the view (*USRSPC) had an Authorization List to secure the object, re-saving the view would remove the Authorization List.
- The Viewpoint Universal drivers for an ADS or JDE connection was initially designed with an internal limit for the longest string that can be passed of 9999 characters. The limit was increase to 20,000.
- OPNSQLF on a DDM file at V7R2 failed with the error ‘Value specified in UFCB (User File Control Block) is not valid at offset 151’.
- Some views return a "SQL0305 Indicator Variable Required" error when no records are returned using *LOCALSYS connection.
- Versioned output was not produced if the target folder did not exist. Now the folder will automatically be created.
- Using EXECUTE to email PDF results when no records are selected, and user defaults are set to ‘Send if No Data No’ results in error EML0070 ‘Spool file attributes not eligible for selection’.
- When emailing view or host table output that exceeds 132 width and no records were selected, and the user defaults were set to send the email, the email was not sent because the second, not the first spooled file was searched for.
- Added EDTWRD and EDTCDE handling when using SERVER(*LOCALSYS) or SERVER(*LOCAL). f the underlying column/table/schema for a given result column can be determined, the edit word/edit code, if one exists for a numeric field, will be displayed as follows (in order of precedence): 1)The value provided by EDTCDE() or /*<S-EDTCDE()-S>*/ in the SQL, 2)The value provided by EDTWRD() or /*<S-EDTWRD()-S>*/ in the SQL, 3)The edit code or edit word associated with the field in the DDS, 4)The edit code J.
- Within the user defaults (SETDFT), a prompt was added for selection of Default Data Base (SERVER).
- SETAUDDFT command will not error when there are more 9,999 user data areas.
- EXECUTE command now retains field text when creating output files when using SERVER(*LOCAL) or SERVER(*LOCALSYS).
- Using DSPVIEWD to generate CL source generates MCH0603 ‘an array index is out of range’ in program DSPVIEWD1 in rare instances when the formatted SQL string uses spaces at the end of the line feed.
- Referencing a UDF without the qualified library that is not in the SEQUEL or SCSERVER10 library, generates an error that the item was not found in *LIBL, even though the UDF library is part of the Viewpoint or Green Screen job. Introduced in Mod 4.
- Character fields defined with CCSID 65535 are unreadable when using Viewpoint Universal Driver for Advanced Database Security or JDE connections.
- Using EXECUTE command, when no records are selected, still sends and email when SETDFT E-Mail Distribution flag is set to N. Mail should only be sent if ‘Send if no Data’ is set to Y.
- All commands changed to Allow Limited Users *NO.
- EXECUTE command creates output fields without field text when using SERVER(*LOCALSYS) or SERVER(*LOCAL).
- Convert/Import Query (CVTQRY) command errors when the % sign is used in LIKE comparisons.
- A host report over a *LOCALSYS view with runtime prompting ( SQLVIEWMP ) does not create a proper report definition (PRTRPTD).
- Error RNX0100 ‘Length or start position is out of range for the string operation in SWPGETOBJD’ displays when saving a Client Table if the view has exactly 60 fields on the SELECT clause.
- EXECUTE to an outfile sporadically displays message ‘MCH1210 in *PGM QRYO03 at instruction x'00EE'’ with views that are SERVER(*LOCALSYS) SYNTAX(*SEQUEL).
- Running a Script View where the SCRETURN command has a TEXT parameter shows the script object text at runtime, not the text specified on the parameter.
- Using the CHGVAR command in a script with a SERVER parameter to a remote database, fails to return a value.
- ANZAUDDTA command fails with duplicate timestamps CPF5149 ‘Operation for program device or member CPIALL is not valid’.
- When using SYNTAX(*SERVER), the Edit Code (EDTCDE) and Column Heading (COLHDG) values will now be supported.
- A new value for the user defaults for the Security setting of *ADS has been created. This setting makes it easier to enforce the Advanced Database Security settings.
- Issue corrected when DSPVIEWD is used to create a source file, where derived fields with a certain length expression caused the line to break inappropriately, depending on the length of the keyword that follows the expression.
- DSPVIEWD now supports the REMARKS parameter of a view, and wild card names like CUST* when used to create a source file.
- For the JOBQ parameter of the BCHEXECUTE, BCHPRINT, BCHREPORT, RUNSCRIPT, RUNSCRIPTV and RUNSCRVPT commands, *JOB was removed as it was not a valid value.
- The SEQUEL object ZLIB was renamed to SEQZLIB to avoid potential conflicts with objects of the same name.
- Issue fixed where loading the Query Audit data (ANZAUDDTA) errored with ‘CPF5009 and CPF5026 duplicate key in QRYSUM when the timestamp of the requests is identical’.
- The runtime SQL conversion for SERVER(*LOCALSYS) and SYNTAX(*SEQUEL) now supports CHAR(date, format) function with the formats JUL, JL1, MDY, YMD and DMY.
- Issue corrected where scripts were incorrectly parsing out a RUNCMD command if prompted views are used in both the CMD and VIEW parameters of the command.
- VALID_DATE function loops causing job to hang in rare instances.
- Using the JDE driver, the descriptions of date fields were not displayed correctly.
- Security setting *STRICT always gave error when view was SERVER(*LOCALSYS) and SYNTAX(*SERVER).
The following updates are included in this version:
- DSPVIEWD now supports syntax of *LOCALSYS/*SERVER and will exclude host table objects (*SQLTBLV).
- Within the user defaults (SETDFT) the expected behavior of the new Query Time Out setting is that if there is a value there (*NOMAX for example), that setting will be used, but if its left blank, the Query Time out Setting in the QAQQINI file will be used.
- EDIT Function may result in the decimal being moved 1 position to the right for some fields.
- Script Views that contain variables, do not retain the Description Text when they are saved. It keeps the default value of SEQUEL Script.
- RUNSCRIPT was not handling SETVAR for EXPR variable with one value using all single quotes.
- DSPSCRIPTD TYPE(*SRCCL) fails with ‘RNX0100 Length or Start position out of range’.
The following enhancements are included in this version:
- Excel and Access can now be referenced as a view data source.
- Update Microsoft JDBC driver to 1.2 with hot fix for greater compatibility.
The following updates are included in this version:
- Runtime SQL conversion (SERVER(*LOCALSYS) and SYNTAX(*SEQUEL) cause error with the CEILING function.
- A view with SERVER(*LOCALSYS) and Alt-named fields where the name contains the # character, produces quotes around the name which causes a syntax error.
- When running command SCHSCRIPT to search for a given text string, a spooled file report is generated called SCRFNDRPL. Overprinting was changed to make the column headings visible.
- REPORT and REPORTVPT commands allow *XLS for the PCFMT parameter when running Client Reports.
- JDE driver connection caused error running view when a Julian date field is used in a CASE statement in SELECT clause with DATE function wrapped around it and same column added in GROUP BY. The JDE driver by default adds the DATE wrapper (if already not added) around such columns (JDE Julian date format column), hence the converted date field in GROUP BY clause was getting customized with Date() causing an error.