Hi, I want to add a security on a sensitive table when I delete lines with an SQL request on a DB2 table. I want to mimic the way MySQL allows you to limit the numbers of rows deleted in an SQL request. Basically I want to do this with DB2 : DELETE FROM table WHERE info = '1' LIMIT 1 Is there a way to do that with DB2 ? ...
I need to check whether a given date is in the preceding month for a monthly query. I can get CURRENT DATE - 1 MONTH to compare the selected date with, but I can't assume the current date will be the first day of each month exactly. Is there a built in way to get the first day of the month without extracting the year and month and glu...
Hi - I'm querying an Iseries from ODBC in my app and am trying to perform a query that returns results from 2 tables. I need to join the tables but the tables are in different libraries. I don't want to use library identifiers in my query as my libraries change as I move from dev>qa>prod. However, I am certain that these tables will only...
I want to transfer data from a vertical db layout like this: --------------------- | ID | Type | Value | --------------------- | 1 | 10 | 111 | --------------------- | 1 | 14 | 222 | --------------------- | 2 | 10 | 333 | --------------------- | 2 | 25 | 444 | --------------------- to a horizontal one: ---------...
The problem that I am facing is primarily on Exception Handling! When an exception occurs I want to put that data in another log table with the error message. However, in DB2 I am not able to figure out a way to retrieve the corresponding error message for the raised SQLSTATE. PS: I have a stored procedure for this migration and I am n...
I got two tables Product(Productname VARCHAR(10), Rating REAL) and Review(RatingID INT, Productname VARCHAR(10), Rating INT). The Rating of the product is the average of all Ratings given in reviews for that specifig product. I'd like to have a trigger, that updates a product whenever a new review is inserted or update, but I just can't ...
How do I use DB2's Explain function? -- both to run it, and to use it to optimize queries. Is there a better tool available for DB2? I've built queries before, but the only way I've had to tell how long they'd take is to run them and time them -- which is hardly ideal. Edit: The answer for me turned out to be "You can't. You don't ha...
Hi, The problem that I am facing is primarily on Exception Handling! When an exception occurs I want to put that data in another log table with the error message. However, in DB2 I am not able to figure out a way to retrieve the corresponding error message for the raised SQLSTATE. PS: I have a stored procedure for this migration and I...
Hi all, I have an oracle script that I am trying to convert to valid db2 syntax. Within this sql file I have various calls to other sql files passing in a parameter using the '@' syntax. e.g. @script1 param1 @script2 param2 Can anyone help me with valid db2 equivalent statements? Is there an equivalent run command in db2? is it po...
I know we're rare, us poor folk that are using iSeries for DB2/AS400, but I'm hoping someone can answer this simple question. Is there any way to return the identity value from an insert statement without using two lines of SQL? I'm being forced to use inline SQL in C# to perform an insert, and then I need to use the identity generated...
Hello I am trying to make db2 work in glassfish with no luck at all. I've read the documentation, but no matter what I do I end up stuck with an error when I do a ping. I put the jar files for the db2 driver and connectivity in the classpath prefix, added them to the lib directory of the domain and no go. What am I doing wrong? Thank yo...
If I have an instance of a JDBC DB2 connection, how do I get the current schema? Either a SQL statement would work, or just a JDBC method call. ...
I'm trying to display a DATE field fetched from a DB2 instance. In Oracle I'd use something like: to_char(v_date, 'YYYY-MM-DD') What's the equivalent in AS400 DB2? ...
I am running a query from my java based web app running in a Websphere container. This query however, being pretty simple, fails with a weird erorr as follows: [5/15/09 16:50:33:828 IST] 0000001e SystemErr R com.ibm.db2.jcc.b.zd: Invalid data conversion:Requested conversion would result in a loss of precision of 40000 [5/15/09 16:...
I'm working with an SSIS Package that pulls data from a DB2 source, runs through a conversion process (unicode stuff) and then stores the data in a SQL table. From the error information below, I have been able to determine that there is some kind of special characters in the DB2 file/table. What I do not know is how I can narrow down w...
Dates in DB2 AS/400 are an integer, containing the number of days since sometime around the turn of the 20th century. Question 1: Does anyone know the IBM DB2/AS400 "zero" date? e.g.: 12/30/1899 12/31/1899 1/1/1900 Question 2: Given an "AS/400" date (e.g. 40010) how can you convert that to a CLR DateTime? DateTime d = new DateTime(...
I have to search a DB2 database for a string that is a concatination of two columns. Is there a way to CONCAT the columns then use the combined string in the WHERE clause? I've searched but can't seem to find an answer to this. Thanks ...
I have created a Database in DB2 and tables with relationships. I would like to create a ER diagram based on my database design in DB2. MS SQL has a facility to create ER diagrams from DB schema, but DB2 doesn't seem to have one, at least to my knowledge. Any one know of any open source tools/facility within DB2 itself for this? ...
Does anyone know of a product similar to Red-Gate's SQL Compare that targets DB2? I am aware of DBDiff but need a tool that compares schema and stored procedures. Update: After some more google searching I will try using either UDB Workbench and DB Solo. ...
I have a table with State, City, Town, and Zip code. The zip code has duplicate data since some zip codes have multiple towns. Now I need a query to return all 4 columns, however I do not want to repeat the zip code in the results. I only want to return 1 record per zip code. Where there are multiple towns I would like to return "-------...