Hot answers tagged

7

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 ...


6

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)


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


5

I've just come across StandardCDC which might be of interest: StandardCDC captures data manipulation language (DML) changes for a specified table and stores the results in a relational format. The capture table mirrors the column list of the tracked object, with options for storing only specific columns.


5

You have a working query, but you are selecting: FROM CF30, EC01, OC02, OM01U1, RS2101F With no explicit joins and only one implicit join: WHERE OM01U1.OM01015 = RS2101F.OUTNUM This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say: FROM OM01U1 INNER JOIN RS2101F ON OM01U1.OM01015 = ...


5

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.


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.


4

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 ...


4

Triggers and audit/history tables. And columns in the main table for Inserted/Updated A history table is easily queried and can be demonstrated to an auditor Universal across different RDBMS No history write = no write because the trigger is part of the transaction From the audit/history tables you can get the state of the database at any point in time. ...


3

Other options except the ones wrote by Aaron and gbn: use a 3rd party audit tool (redgate, lumigent...) implement custom package using fn_dblog system function (reads the transaction log) use 24/7 server traces (already mentioned by Aaron, sorry)


3

Change tracking can be used in Standard Edition. It will allow you to track changes to the data, but won't be able to tell you who made them. Your options are basically the following: triggers - maybe even use instead of triggers - for an update you can move the current copy of the row to a history table, and replace it with the active copy. move to ...


3

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 ...


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 ...


3

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 , ...


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

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

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.


3

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 ...


2

You are joining 5 files, but have only one condition that limits one of those joins. WHERE OM01U1.OM01015 = RS2101F.OUTNUM This condition should go into your join specs FROM OM01U1 JOIN RS2101F ON OM01U1.OM01015 = RS2101F.OUTNUM JOIN CF30 ON _____ = ______ JOIN EC01 ON _____ = ______ JOIN OC02 ON _____ = ______ If some of these ...


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

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 ...


2

Actually, you question has less to do with "DB2" than it does with one of IBM's main products - IBM i on Power (also known as System i, iSeries, and AS/400 in the past). For whatever reason, IBM chooses to refer to a lot of things as "Objects" within their system. This gets very confusing to hear, because in and of itself, the operating system i5/OS is not ...


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 ...


1

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 ...


1

Start with this statement before trying to execute the function: SET PATH TESTDAT;


1

iSeries Navigator did not "return" two statements. It echoed them in the messages tab, NOT the data results tab. SYSIBM.SYSDUMMY1 is an IBM supplied table with 1 column and one row. You got the correct answer for the procedure you wrote. SYSDUMMY1 is generally used in example code as a proxy to evaluate expressions that do not require any table. For ...


1

Using System i Navigator, you can run your queries in there to see if there are more indexes that may be recommended. The system tries it best to create the right indexes before running and caches those access paths. One thing I did in my last project with some larger queries was to create procedures. Then the system stores those access paths and speeds up ...



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