Hot answers tagged

10

I don't know about iSeries7 but some DBMS (like Oracle) allow this - as long as t2 (b) is a unique or primary key: UPDATE ( SELECT t1.a AS t1a, t2.a AS t2a FROM t1 INNER JOIN t2 ON t2.b = t1.b ) u SET t1a = t2a ; Tested at (Oracle 11g) SQL-Fiddle Other DBMS allow this syntax (like SQL-Server which doesn't make any fuss about the ...


7

Try using the MERGE statement from SQL2003 Standard - which is the equivalent of UPDATE..SET..FROM syntax. Example: MERGE INTO dbo.t1 AS Target USING (SELECT a,b,c FROM dbo.t2) AS Source ON (Target.b = Source.b) WHEN MATCHED THEN UPDATE SET Target.a = Source.a Note: for iSeries, MERGE requires INTO (credit: Jeff Stevens)


6

I've tested two tools that can provide info about transactions including when, who, using which computer and application ApexSQL Audit creates auditing triggers for you, so if you're not up to coding and creating triggers for each of your tables, this is an option It has 2 built in reports, and the good thing about it is that it saves all captured ...


6

Add WITH REPLACE to the end of your DECLARE GLOBAL TEMPORARY TABLE statement. This will create your table in QTEMP regardless of whether one already exists or not.


6

In addition to setting Local DTC Properties, you need to open the firewall on both machines to allow program Distributed Transaction Coordinator to go thru. When testing T-SQL in Query Editor Window, make sure to turn on XACT_ABORT as suggested by Microsoft. XACT_ABORT must be set ON for data modification statements in an implicit or explicit ...


5

There is an IMPLICITLY HIDDEN column attribute that can be applied. Please check for this... This means the column will only appear when it is explicitly specified on the SELECT statement. Check the CREATE TABLE statement for this property, or materialize the TABLE's DDL using some API or tool, such as RTVSQLSRC.


5

When you use openquery the query itself executed on remote server and you receive only results. In case of linked server, you local server does all the job. You may want to check http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/0e68f512-1e19-4c50-b343-219085d70076


4

Figured it out!! Apparently we didn't have the latest service pack installed. We had to install the 64bit version of the service pack (32bit version failed to install), and when it finished the decimal numbers were coming across. EDIT: In case anyone was wondering: We're using "IBM iAccess for Windows 7.1 (07.01.0800) (64bit)" and using "Microsoft OLE DB ...


4

You are not checking for an end of cursor condition. DECLARE END_CL INT DEFAULT 0; DECLARE CL CURSOR FOR SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS FROM DB_TRANSDATA ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_CL = 1; FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , ...


4

I believe this is a bug with the specific version of IBM's driver I was using. I was using the ODBC driver from IBM i Access Client Solutions Linux AP v07.03.00. I replaced that driver with the ODBC driver from IBM i Access for Linux v7.1.0-1.0 and now everything works as expected.


4

The defect with only the first character of the column name being returned is described in IBM APAR SE63700: http://www-912.ibm.com/n_dir/nas4apar.NSF/b6c9b771e38b5ea2862564c00079d110/0f43bb0435716de586257f340041ed7a?OpenDocument The fix should be integrated into the new version of the IBM Access Client Solutions Application Package for Linux, due mid-July ...


3

You are experiencing the difference between "system naming convention" and "SQL naming convention" modes. Originally, system naming mode required a slash character / as the delimiter when qualifying with a schema name, while SQL naming mode used a period . delimiter. An update to v7.1 now allows either delimiter when using system naming. V5R3 is no longer ...


3

This should probably be a comment rather than an answer since I'm not sure if my reasoning is correct, but then I wouldn't be able to have any formatting. Could you create a temporary table and JOIN instead? ...<sql statements and joins> JOIN tmp_table ON my_table.a = tmp_table AND my_table.b = tmp_table.b Edit: if the values you're checking against ...


3

You can use a windowing function to partition the data. If I knew your data better I'd rewrite it without a subselect, but the wall of text is making my head hurt ;) Here you go: SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY OM01U1.OM01015 ORDER BY EC01_LASMTCDAT DESC) as r, OM01U1.OM01041 as Loc, OM01U1.OM01015 AS Outlet, -- SNIP -- -- SNIP -- ...


3

EXECUTE ... USING requires host variable names, not expressions, while SUBSTRING(whatever) is an expression. You will need to declare four host variables, assign results of the SUBSTRING(...) expressions to them, then use those variables in the EXECUTE statement. Alternatively, you could move expressions to the dynamic statement itself: set ...


3

Why are you using a table? The IBM i provides a native data queue object... If your application is running off platform in a language that doesn't offer direct access to the native objects, you could always build a stored procedure or user defined function on the I to provide access to the data queue. Having said that, the answer to your question is the ...


2

In SQL Server you actually shouldn't need to make any changes to the outlying code or add an INSTEAD OF trigger to make this work. Here is a quick example, tested on SQL Server 2012, but should work fine on 2005 as well: CREATE TABLE dbo.source(bar INT, x UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID()); GO CREATE TABLE dbo.[target](bar INT, x UNIQUEIDENTIFIER ...


2

One thing that you are missing in your stored procedure declaration is the DYNAMIC RESULT SETS option. The default value for that option is 0, may be that's what confuses the client software. Whether it (the client software) can even handle cursors returned by a stored procedure is another story.


2

The solution to reporting on iSeries SQL stored procedure in SSRS is to install DB2UDB .Net provider that comes with IBM Client Access into BIDS by adding two XML tags to RSReportDesigner.config. Add the following to <Data> tag in .config: <Extension Name="DB2UDB" Type="IBM.Data.DB2.iSeries.iDB2Connection,IBM.Data.DB2.iSeries, Version=12.0.0.0, ...


2

The solution is simple: let AS400 convert the value in float because implicit conversion applied by the iSeries provider fails almost everytime. So, very simply: select * FROM openquery(NADS, 'select CMD, cast(1.23 as float) Type from CREDD') In this way all works fine: 1.23 will be float in Sql Server too


2

As Heinz Z. did, I discovered the problem. One of your function parameters is char, while you pass the string literal 'ANY', which is considered a varchar. The database engine looks for an overloaded version of the function with varchar parameters, but doesn't find it. Solution is either changing function parameter to varchar or cast parameter to char in ...


2

Well, I did a lot of searching, found some information on the SQL0204 error, which led me to this support page: https://www-01.ibm.com/support/docview.wss?uid=nas8N1016377 which has instructions on what commands to run on the server if you see that SQL0204 error. I forwarded that on to the partners IT guy, who tried it (and a few other commands according ...


2

The manual says WHEN MATCHED does not allow INSERT. However, you should be able to use the expire_date condition in the USING clause: MERGE INTO products AS P USING ( VALUES ( ... ) ) AS S(CUSTOMER,REGION,DTL1,DTL2,DTL3,COUNT,LAST_DATE,FLAG,ORDER,PIECES) ON s.CUSTOMER = p.CUSTOMER and s.REGION = p.REGION and ... and p.expire_date > current_date ...


1

It appears to be long column name versus short column name. Upon closer inspection the hidden column always had the same value as an unhidden one. Since the long name in this case was abbreviated and cryptic, I didn't make that connection right away.


1

If the foreign key constraints don't point to any other table inside of originating database (ie, if SQL Server doesn't point to SQL Server, etc.), then I would drop those foreign keys. They aren't useful. If they do point to other tables, then perhaps you do have to copy what data is needed just to satisfy the constraint. (Ugh). Either that or re-consider ...


1

I'm no expert on DB2 for iSeries (my only experience with it is helping others online!), so this may be entirely wrong... I think support for the standard INFORMATION_SCHEMA was only added recently. As in DB2 for LUW (which I'm rather more familiar with), INFORMATION_SCHEMA is probably just a relatively thin set of views over the "real" system catalog which ...


1

Each of these four things should be given its own PF (aka table). In the real world, any of them might even be best served by additional supplementary tables, as may be appropriate. For example, suppose a customer wishes to pace an order for several different items. This would normally be handled with an order header file, to store data pertaining to the ...


1

As I understand it, your development team got the error when using DbVisualizer with JTOpen (jt400.jar) for direct connections to the database. I got the same exception with DBvis ver 9.1.10. The tool took a long time when clicking the connect button, then finally showed error code -99999. What I do to fix this is to disable the Proxy setting of DBvis: ...


1

IBMi DB2 Numeric Data Types: RPGLE - DESCRIPTION - DB2 SQL P - Packed decimal - DECIMAL - DEC S - Zoned decimal - NUMERIC - NUM


1

The problem was something simple yet hard to find. The indexes' sort sequence was wrong. The clients were wrongly configured to use language preference for sort sequence. So when the indexes were created they had language specification for sorting. The server when a job was submitted, was looking for indexes with *HEX specification so he was ignoring ...


Only top voted, non community-wiki answers of a minimum length are eligible