Ted Holt
-
What Happened to My Key?
June 25, 2008 Hey, Ted
… Read moreWe use SQL’s CREATE TABLE command to make each user his own copy of a file. Although the original file is keyed, the copy is not. Do you have any ideas on how to keep the key?
–Mary Jo
Mary Jo provided the following commands by way of example:
create table Temp as (select * from PATTERN) with no data rename table Temp to FileForJoeSmith
So, what’s Mary Jo to do?
The first thought I had was to create the index by hand, like this:
create unique index FileForJoeSmithIndex1 on FileForJoeSmith (SomeField)
But that was not a solution to the
-
Replace the Contents of a Physical File That Has Triggers
May 7, 2008 Hey, Ted
… Read moreI need to copy the contents of a physical file from our production system to its counterpart in our development system, which is a separate logical partition. I have several ways to copy the file from the production system to the development system. However, error messages I get say that I cannot replace the data because the database file has triggers over it. Help!
–Tricia
You need to disable the triggers. Disabling triggers is most easily done if all of the triggers are active. Use Change Physical File (CHGPF) to disable the triggers.
CHGPFTRG FILE(MYLIB/MYFILE) TRG(*ALL) STATE(*DISABLED)
Then copy the
-
Multiformat SQL Data Sets
April 30, 2008 Hey, Ted
… Read moreDDS-defined logical files can have multiple record formats, each one of them coming from different physical files of different types of data. I would like to do the same sort of thing in SQL. That is, I want to retrieve all the records from one file followed by all the records from a second file, grouped by one or more common key fields. This is not a join, and it doesn’t seem like a union either, because the two data sets are so different. Am I trying to do the impossible?
–David
What you’re doing may be unusual, but it’s
-
Build Pivot Tables over DB2 Data
April 30, 2008 Hey, Ted
… Read moreIf you already know about these, then just hit the ol’ delete key on the message. I learned how to do this today. SQL is great for going “down the page.” It’s when they want data summed across that it gets to be a real kludge! Pivot tables are the answer.
It started with your article Load a Spreadsheet from a DB2/400 Database. I got it working! Sweet! Miracles never cease! Thanks a bunch!
Once the data is loaded into the spreadsheet via the SQL statement, make sure the column headings have decent labels. Open the Data menu and
-
A Recycle Bin for the IFS (Sort Of)
April 23, 2008 Hey, Ted
… Read moreWe inadvertently deleted an IFS file that was created today and therefore was not on the previous night’s backup. What I wouldn’t give for an IFS recycle bin! We can recreate the file, but I wonder, short of backing everything up every minute, if there is anything that I could have done to prepare for such a situation?
-Chris
My sympathy, Chris. I hate it when that happens. As you point out, the IFS has no recycle bin, but there is a way you can delete a file from a directory without deleting it from disk. I’ll show you the
-
One Save File from More than One Library
March 26, 2008 Hey, Ted
… Read moreI would like to place objects from several libraries in a save file. When I run a Save Library (SAVLIB) or Save Object (SAVOBJ) command that specifies more than one library, I receive message CPF3789: Only one library allowed with specified parameters. I really don’t want a bunch of save files. Is there another way?
–Jackie
A save file can contain other save files, so here’s a method you can try. To keep it simple, let’s say you want to save the contents of two libraries–MYLIB1 and MYLIB2–to one save file–SOMELIB/SOMESAVF.
1. Create a save file for each library.
CRTSAVF
-
Grouping a Union
March 19, 2008 Hey, Ted
… Read moreWe have several sales history files–one for each year. When I need to combine data for more than one year, I have to use an SQL UNION. I am trying to group “unioned” data and can’t seem to get it right. Is it possible to group a dataset that is built by a union?
–Tom
This appears to be an easy problem to resolve, but appearances can be deceiving. If you’re not careful, you can get the wrong results when you UNION two or more tables, especially when you’re summarizing data.
Divide the query into two steps: one to union
-
More About SQL Correlation Names
March 12, 2008 Hey, Ted
… Read moreI hope my question is an easy one to answer. I have a file that stores the location of inventory in a warehouse. Location consists of a row (an aisle), column, and level. Why will an SQL UPDATE let me change the column and level, but not the row?
–Scott
You can update the column like this:
update qtemp/inventory set column=4 where item = 'SL-701'
But updating the row gives you error SQL0104. (Token 4 was not valid. Valid tokens: ( : DAY CAST CHAR DATE DAYS HOUOUR LEFT TIME TRIM YEAR COUNT MONTH.)
update qtemp/inventory set row=4 where item
-
Don’t Let SQL Name Your Baby, Take 2
March 5, 2008 Hey, Ted
… Read moreDon’t Let SQL Name Your Baby gave some good advice on tackling the problems created by column names longer than 10 characters. Table names of more than 10 characters can also cause problems, especially if you need to add the same table on multiple systems. Plus, if you want to do updates in an RPG program, it’s nice to have a record format name different from the table name.
–Tim
Tim points out another discrepancy between the System i’s QSYS.LIB file system and relational databases that run on other systems. Using SQL, you can create a table with a very
-
LPEX Edit in Hex Mode
February 20, 2008 Hey, Ted
… Read moreThanks for your article, Let WDSc Help You Format Your Source Code. Prior to using WDSc, I used a tool to colorize my RPG source code. It applied different colors to various types of lines (comments, loop structures, etc.). LPEX doesn’t seem to be able to understand the hex code and displays box symbols for the hex value. Is there something in the parser controls that will make LPEX recognize the hex value or simply ignore it?
–Eddie
I don’t know if there’s anything in those parser controls that will make LPEX interpret those p-field codes or not, Eddie.