TechTips / SQL


TechTip: SQL Date and Time Arithmetic PDF Print E-mail
Tips & Techniques - SQL
Written by Jim Cooper   
Friday, 20 February 2015 00:00

Are you looking for a hands-on approach that provides start-to-finish coverage of database design and SQL

Written by James Cooper

Editor's note: This TechTip is an excerpt from Chapter 6, "Single-Table Queries," of Database Design and SQL for DB2 (MC Press, 2013).

 

SQL on IBM i includes expressions and functions for working with date and time values. Externally, date values are character strings containing numbers for the year, month, and day and (in some formats) date separators. When a literal date is entered, the value is coded as a string. The format of the string depends on the values specified for the DATFMT and DATSEP parameters. For example, if *ISO format is specified, the following string represents May 1, 2011:

Add a comment
Last Updated on Thursday, 19 February 2015 11:25
Read more...
 
User Rating: / 1
PoorBest 
TechTip: More on Combining Multiple Row Values into a Single Row with SQL in DB2 for i PDF Print E-mail
Tips & Techniques - SQL
Written by Kent Milligan   
Friday, 13 February 2015 00:00

Leverage recursive table expressions to pivot row values into a single column.

kent milligan 117x117Written by Kent Milligan

After my last article, I had a number of readers express interest in a solution that would work on servers running pre-IBM i 7.1 operating system release levels. Luckily, recursive SQL capabilities were first made available with the DB2 for i V5R4 release, so this article will provide a solution that can run on release levels of V5R4 and newer.

Add a comment
Last Updated on Friday, 13 February 2015 00:00
Read more...
 
TechTip: Combining Multiple Row Values into a Single Row with SQL in DB2 for i PDF Print E-mail
Tips & Techniques - SQL
Written by Kent Milligan   
Friday, 09 January 2015 00:00

Leverage recursive SQL to pivot row values into a single column.

kent milligan 117x117Written by Kent Milligan

Often, when looking at a report, a user prefers to see similar values grouped together on a single line of output instead of having those values spread across multiple lines of report. An example of that type of report is displayed in the table below. For each automobile make, the report lists all of the models that are currently in inventory in a single row of the report.

Add a comment
Last Updated on Wednesday, 14 January 2015 09:45
Read more...
 
User Rating: / 1
PoorBest 
TechTip: Enhanced Debug Experience for SQL Routines in IBM i PDF Print E-mail
Tips & Techniques - SQL
Written by Kent Milligan   
Friday, 05 December 2014 00:00

New enhancements simplify the debug of SQL routines.

kent milligan 117x117Written by Kent Milligan

IBM first improved the debugging of SQL stored procedures, functions, and triggers way back in the V5R2 release when it introduced the *SOURCE debug view. This debug view allowed IBM i developers to debug their original SQL source code instead of having to debug the ugly C code that DB2 for i generates behind the scenes for SQL routines.

Add a comment
Last Updated on Friday, 05 December 2014 00:00
Read more...
 
User Rating: / 1
PoorBest 
TechTip: A Hidden Tax with SQL Programs and SQL Packages PDF Print E-mail
Tips & Techniques - SQL
Written by Michael Sansoterra   
Friday, 12 September 2014 00:00

If your environment shares one set of embedded SQL programs or SQL packages among multiple library lists, you may be paying a performance penalty for access plan rebuilds.
Written by Michael Sansoterra

Thanks to the spectacular library list concept, it's common to have multiple instances of application data libraries on the IBM i. Further, a typical IBM i application stores its programs in one or more object libraries, separate from the data libraries. This configuration allows a single set of application programs to operate on multiple, independent data libraries.

Add a comment
Last Updated on Friday, 12 September 2014 00:00
Read more...
 
User Rating: / 2
PoorBest 
TechTip: Compound Dynamic SQL Statements PDF Print E-mail
Tips & Techniques - SQL
Written by David Shirey   
Friday, 04 April 2014 00:00

Dynamic? What's that all about?

david shireyWritten by David Shirey

A lot of new things came out in the IBM i Technology Refresh for 7.1 that was released this past fall. And some of those enhancements have been for DB2, the DDL-based database that's part of the i operating system. The only one of those enhancements that we're going to look at today is the one for Compound Dynamic SQL statements, which were added as part of PTF Group SF99701, Level 26.

Add a comment
Last Updated on Tuesday, 01 April 2014 14:35
Read more...
 
TechTip: Get to the Time Machine, Marty! PDF Print E-mail
Tips & Techniques - SQL
Written by Scott Forstie   
Friday, 31 January 2014 00:00

Let's make a quick trip back to six years ago.

Written by Scott Forstie

In 2007, with the tireless editorial help from Skip Marchesani, I wrote an all-inclusive article entitled "Procedures, and Functions, and Catalogs! Oh My!". The fine folks at MC Press published the article in February 2008.

 

This article has served its intended purpose, providing customers and IBMers alike a resource to bridge the gap between the SQL Reference and the successful deployment and maintenance of SQL routines with DB2 for i. Even though I lack a DeLorean time machine, I feel compelled to return to this article and provide a few updates.

Add a comment

Last Updated on Tuesday, 28 January 2014 16:07
Read more...
 
User Rating: / 2
PoorBest 
Partner TechTip: Three Simple Steps to Modernize Your Reports PDF Print E-mail
Tips & Techniques - SQL
Written by Mike Stegeman   
Monday, 18 November 2013 00:00

Update your reports with SQL and SEQUEL.

mike stegemanWritten by Mike Stegeman

You've heard that utilizing SQL is the most efficient way to access your data, but maybe you're hesitant to change your data access solution because of the potential learning curve. Your users are asking for updated reports, dashboards, shortcuts, web access, and more, but some legacy software's queries look "old fashioned." How can you deploy improvements? There are three easy steps.

Add a comment
Last Updated on Thursday, 14 November 2013 14:38
Read more...
 
<< Start < Prev 1 2 3 4 5 6 7 8 9 10 Next > End >>

Page 1 of 11

WEBCAST

MC Webcast CenterFEATURED WEBCAST

See What i Can Do With Modern RPG Development

Your applications deserve more than just a new look! Change the perception of the IBM i as “old” with modern development tips from IBM and Profound Logic Software.
Watch as Barbara Morris, Brian May and Alex Roytman dive beneath the GUI surface to show the benefits of:

 

  • Working with free-format RPG in IBM i TR7
  • Breaking free from “The Cycle” of traditional RPG development
  • Going beyond the 10 character limit in display and database files
  • And more!

You'll also see a live demonstration of these techniques as the presenters create a modern web application before your eyes! 

 

Watch the Webinar Now!

 

TRIAL SOFTWARE

MS Office Connector for Query/400

NGS' Qport Office enables Windows users to run IBM Query/400 queries to: 

 - Create and update Excel spreadsheets and Access databases

 - Create Word documents

 - Send to Windows screen and PC printers

No query conversion is required. Works with i5/OS V5R1 & above. Installs in minutes!

If you don’t have a budget to replace IBM Query/400, but want your users to have one click enhanced output of their queries.… Request the online license agreement and product download instructions today!

Offer good through December 31, 2013.

   MC-STORE.COM