[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Iseries Database edit

Posted on 2009-12-17
Medium Priority
Last Modified: 2013-12-06
  I am trying to edit a couple of rows in a table with iseries navigator. problem, there are thousands of rows. I might be crazy, but I cannot find any search funtion to find the rows that I would like to edit. Is a SQL script the only way I am going to be able to change this? Is there a better editing tool out there?

Thank you
Question by:mfleming07
LVL 18

Accepted Solution

Dave Ford earned 1000 total points
ID: 26074526

SQL scripts are definitely the best way to go, but there are a few other alternatives.

I know there's a third-party tool called DBU that does this pretty well, but (of course) it costs moeny.

Alternately, you could StrDfu (from the green-screen) and use the option to "Update data using temporary program". But, while that does work, it's pretty clumsy.

IMHO, sql scripts are the best bet.


Author Comment

ID: 26074578
When I make a SQL script, I find the rows that I need. But it is in view mode, cannot update.. Do I need to write an update row portion?
LVL 36

Assisted Solution

by:Gary Patterson
Gary Patterson earned 1000 total points
ID: 26074731
One way (and the way I usually do it) is to use the RUN SQL Scripts window in Navigator (right-click int he database name), and first do a SELECT query:

FROM table1
WHERE lastname = 'Patterson' AND firstname = 'Gary'
Verify that the SELECT gets you the record or records you want to change, and then compose and execute an UPDATE query, cutting and pasting the WHERE clause from your SELECT:

UPDATE table1
SET url = 'www.quorumresources.com'
WHERE lastname = 'Patterson' AND firstname = 'Gary'
Of course you need to be very careful with UPDATE and DELETE queries, since a little slip in syntax can cause a lot of rows to be updated or deleted.  that's why I like the SELECT query first.  Once the WHERE clause is right, if you cut and paste it, you are safe for accidentally selecting the wrong records.

If you want a full-featured data editor, you'll need to look at third party tools - Navigator's limited interface for data editing leaves a lot to be desired.  I recently used AquaData Studio - it is great (but it costs about $100 a seat, if I remember correctly).

There are also a plenty of green-screen tools, as DaveSlash indicated:  DFU is installed on most systems, DBU is a popular 3rd party tool, PFE, and many others.

- Gary Patterson
Check out my EE profile:  http://www.experts-exchange.com/M_4382324.html


Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will discuss all things related to StageFright bug, the most vulnerable bug of android devices.
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question