I need to do periodic commits in my purge function I wrote a purge function that runs in Postgres 9.6. The function goes through all of the tables in our application starting from the bottom most child table and ending with the top most parent table and purges based on date or client. When we run this in production and beta, there are other processes happening at the same time and the database is getting blocking locks based on my uncommitted deletes. One possible solution would be to do a commit every thousand or so rows. But I am new to Postgres and I can't get Postgres 9.6 to do commits within a loop. It's possible that Postgres 9.6 won't do this and maybe Postgres 12 will. We are moving to Postgres 12.
Is there anyway to get the commits to work in Postgres 9.6? I included some test code so that you can test the commits on your own. My actual code is far more complicated.
Thanks
\timing

DROP TABLE IF EXISTS _tmp_test_transactions_table;
CREATE TABLE _tmp_test_transactions_table ( pkey INTEGER );

create or replace function _tmp_test_transactions ( p_number_of_rows INTEGER )
returns int language plpgsql
as
$fun$
DECLARE
   v_counter     INTEGER := 0;
   v_final_count INTEGER := 0;
BEGIN
   START TRANSACTION ISOLATION LEVEL READ COMMITTED;

   LOOP
      v_counter := v_counter + 1;
      EXIT WHEN v_counter > p_number_of_rows;

      IF MOD( v_counter, 10 ) = 0
      THEN
         COMMIT;
         START TRANSACTION ISOLATION LEVEL READ COMMITTED;
      END IF;
   END LOOP;

   SELECT COUNT(*)
     INTO v_final_count
     FROM _tmp_test_transactions_table;

   RAISE NOTICE 'Inserted % rows in the _tmp_test_transactions_table table', v_final_count;
   COMMIT;
END
$fun$;

SELECT _tmp_test_transactions( 100 );
psql:bbyrd_test_transactions.sql:36: ERROR:  unsupported transaction command in PL/pgSQL
CONTEXT:  PL/pgSQL function _tmp_test_transactions(integer) line 6 at SQL statement
Time: 0.473 ms

Transaction control (commit, rollback) is not allowed inside PostgreSQL functions.
Starting with PostgreSQL 11, it's possible in procedures (see CREATE PROCEDURE in the documentation). In fact that's the main difference between functions and procedures.
Before PostgreSQL 11, the ability to commit inside a loop comes from client-side beyond-SQL programming. The psql CLI can of course issue commit and rollback but it doesn't have some of the basic constructs found in programming languages, such as loops.


how to use a variable as a part of a field name in mysql trigger declaration Here is what I want to do (a part of my trigger):

BEGIN
DECLARE updatecount INT default 0;
declare number int;
declare column varchar(10);
declare i int default 1;
declare j int default 1;
while i<=6 do
    set column = "num"+i; /*will be num1 num2, so on*/
    set number = NEW.@column; /* will represent NEW.num1, num2 so on*/

I know that the last 2 lines are not correct. But I wanted you guys to understand what I want to do. Can anyone tell me the correct syntax? thanks.

EDIT:

what I want to do with this trigger is when a new row added into the resultsTBL, I want to check all the rows in the guessesTBL to find how many of them are same.

For example we have 2 rows in guessesTBL (always in ascending order):

num1 num2 num3 num4 num5 num6
 1    5    10   15   20   26
 4    8    12   16   25   40

then we add a new row into the resultsTBL (always in ascending order):

num1 num2 num3 num4 num5 num6
 1    8    17   20   25   40

after that trigger will compare the new added row with all the rows in the guessesTBL:

for the 1st row the result is: 2
for the 2nd row the result is: 3

pseudocode:

CREATE DEFINER = CURRENT_USER TRIGGER `myDB`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
for each field in resultsTBL (num1, num2, ... num6)
  number= num1...num6
  for each field in guessesTBL (num1, num2, ... num6)
    if number == guessesTBL.num1,num2,...num6
      counter++


SQL Server TempDB transaction log Issue I've worked with SQL Server for some time and I understand the various issues that one can run into with TempDB. But, with this latest issue, if I have my issue figured out correctly, is it possible that a statement with a BEGIN TRANSACTION that uses tempdb tables/objects that is left hanging (no commit) can hold the tempdb LOG file "open" and not allow tempdb to clear its own log? This leads to the tempdb log file eventually filling up and causing issues in the SQL Server.  I find this hard to believe that there isn't some sort of safeguard in place within SQL Server that would somehow allow the tempdb LOG to not be affected If not, I find this disturbing…

TempDB is no different than any other database.  All actions must be logged, and log space will only be reused once the log records for a given transaction have either been committed or rolled back.

As @KrisGruttemeyer said in a comment, you can find out what type of action is causing the transaction log to grow by looking at:

select d.name, d.log_reuse_wait_desc
from sys.databases d
where d.name = 'tempdb';

This will likely show you either 'active transaction' or 'nothing' for tempdb.  If you see active transaction you know some transaction is open and has not been committed yet.  You can see what transactions are open by looking at various system DMVs including, sys.dm_tran_active_transactions, among others.

For a much more in-depth look at what is causing your tempdb to grow, check out this question.


Database replication without locking tables I've been asked to setup replication between master and slave database.
The aim is not to lock any tables while dumping database.

I've never setup replication but I have found this guide:

https://plusbryan.com/mysql-replication-without-downtime
He is using these settings.

On master:

server-id=1
binlog-format   = mixed
log-bin=mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1

On slave:

server-id               = 101
binlog-format       = mixed
log_bin                 = mysql-bin
relay-log               = mysql-relay-bin
log-slave-updates = 1
read-only               = 1

Could you please guys advise me if what following settings mean and what impact they will have on whole settings (database is around 35GB)

One more thing. This replication needs to be 'row based' so instead mixed I shall use row option in master settings

innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-slave-updates = 1
read-only               = 1

In don't want to suffer any performance issue or high I/O because of constant disk writes.
As I mentioned I have no experience on settings this up so I'm kindly asking you for help. Many Thanks

this is the meaning of the parameters you pointed:

server-id: its mean the server has unique ID in-order to communicate with other servers, the ID range should be 1-2^32, and if not specified its mean the server ID will be 0 (stand alone) and will not communicate with other servers.
binlog-format: its a binary log format, it has three values, STATEMENT causes logging to be statement based, ROW causes logging to be row based, and MIXED causes logging to use mixed format.
log-bin: this will enable binary logging (https://dev.mysql.com/doc/refman/5.7/en/replication-options.html)
datadir: where you will store your data.
innodb_flush_log_at_trx_commit: Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose up to a second of transactions in a crash. (Is it safe to use innodb_flush_log_at_trx_commit = 2)
sync_binlog: this parameter is use to sync binlog from one server to another.
relay-log: The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.(https://dev.mysql.com/doc/refman/5.7/en/slave-logs-relaylog.html)
log-slave-updates: this option needs to be enabled in order for the auto-positioning mechanism to be active.
read-only: this option will make slave is used for read-only no update (other than the ones come from master server) is allowed, to make sure there will be no conflict


How to create an Event Notification that runs a Job/procedure when mirroring state changes I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL?

Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to understand and make everything he says.

So far I think what I need to create a notification event for DATABASE_MIRRORING_STATE_CHANGE, am I correct?

how can I create this event notification to when its triggered inserts a line in a table, that stores a timestamp and an ID that comes from the notification.

so far I'm setting up one alert per ID, each one running a job like this(this example is  for ID = 1):

    DECLARE @state AS varchar(50);
    SELECT @state = mirroring_state_desc FROM SYS.database_mirroring WHERE mirroring_guid IS NOT NULL;
    IF (@state IS null) SET @state = ' ';
    INSERT INTO MirroringAlerts (DateTime, alertID, alertDesc, Sync, alertCreator) values (SYSDATETIME(), 1, 'Principal synchronized with W ', @state, @@SERVERNAME)

Basically I'm creating an internal log in this database:

CREATE TABLE [dbo].[MirroringAlerts](
    [DateTime] [datetime] NOT NULL,
    [alertID] [smallint] NOT NULL,
    [alertDesc] [nchar](50) NOT NULL,
    [Sync] [nchar](12) NOT NULL,
    [alertCreator] [nchar](128) NULL
) ON [PRIMARY]

But this way...the alerts are not being triggered fast enough...so I'm loosing information...

Can you tell me how to program this behaviour with create event notification for Database Mirroring State Changed event?

Best Regards

Step 1: Create a service to receive the notifications and a queue for it:
use msdb;
go

create queue dbm_notifications_queue;
create service dbm_notification_service
    on queue dbm_notifications_queue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification dbm_notifications
    on server
    for database_mirroring_state_change
    to service N'dbm_notification_service', N'current database';
go

Note that I'm using msdb, this is not an accident. Because the server level event notifications are sent from msdb it is much better if you create the opposite conversation endpoint (the target) also in msdb, which implies that the destination service and the queue must also be deployed in msdb.
Step 2: create the event notification processing procedure:
use msdb;
go

create table dbm_notifications_errors (
    incident_time datetime not null,
    session_id int not null,
    has_rolled_back bit not null,
    [error_number] int not null,
    [error_message] nvarchar(4000) not null,
    [message_body] varbinary(max));
create clustered index cdx_dbm_notifications_errors
    on dbm_notifications_errors  (incident_time);
go

create table mirroring_alerts (
    alert_time datetime not null,
    start_time datetime not null,
    processing_time datetime not null,
    database_id smallint not null,
    database_name sysname not null,
    [state] tinyint not null,
    [text_data] nvarchar(max),
    event_data xml not null);
create clustered index cdx_mirroring_alerts
    on mirroring_alerts (alert_time);
go

create procedure dbm_notifications_procedure
as
begin
    declare @dh uniqueidentifier, @mt sysname, @raw_body varbinary(max), @xml_body xml;

    begin transaction;
    begin try;
        receive top(1)
            @dh = conversation_handle,
            @mt = message_type_name,
            @raw_body = message_body
        from dbm_notifications_queue;
        if N'http://schemas.microsoft.com/SQL/Notifications/EventNotification' = @mt
        begin
            set @xml_body = cast(@raw_body as xml);
             -- shred the XML and process it accordingly
             -- IMPORTANT! IMPORTANT!
             -- DO NOT LOOK AT sys.database_mirroring
             -- The view represents the **CURRENT** state
             -- This message reffers to an **EVENT** that had occured
             -- the current state may or may no be relevant for this **PAST** event
            declare @alert_time datetime
                , @start_time datetime
                , @processing_time datetime = getutcdate()
                , @database_id smallint
                , @database_name sysname
                , @state tinyint
                , @text_data nvarchar(max);

            set @alert_time = @xml_body.value (N'(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME');
            set @start_time = @xml_body.value (N'(//EVENT_INSTANCE/StartTime)[1]', 'DATETIME');
            set @database_id = @xml_body.value (N'(//EVENT_INSTANCE/DatabaseID)[1]', 'SMALLINT');
            set @database_name = @xml_body.value (N'(//EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME');
            set @state = @xml_body.value (N'(//EVENT_INSTANCE/State)[1]', 'TINYINT');
            set @text_data = @xml_body.value (N'(//EVENT_INSTANCE/TextData)[1]', 'NVARCHAR(MAX)');

            insert into mirroring_alerts (
                alert_time,
                start_time,
                processing_time,
                database_id,
                database_name,
                [state],
                text_data,
                event_data)
            values (
                @alert_time,
                @start_time,
                @processing_time,
                @database_id,
                @database_name,
                @state,
                @text_data,
                @xml_body);
        end
        else if N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' = @mt
        begin
        set @xml_body = cast(@raw_body as xml);
        DECLARE @error INT
                , @description NVARCHAR(4000);
        WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)
        SELECT @error = CAST(@xml_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'),
            @description = CAST(@xml_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'NVARCHAR(4000)');

        insert into dbm_notifications_errors(
            incident_time,
            session_id,
            has_rolled_back,
            [error_number],
            [error_message],
            [message_body])
        values (
            getutcdate(),
            @@spid,
            0,
            @error,
            @description,
            @raw_body);
            end conversation @dh;
        end
        else if N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' = @mt
        begin
            end conversation @dh;
        end
        commit;
    end try
    begin catch
        declare @xact_state int = xact_state(),
            @error_number int = error_number(),
            @error_message nvarchar(4000) = error_message(),
            @has_rolled_back bit = 0;
        if @xact_state = -1
        begin
            -- Doomed transaction, it must rollback
            rollback;
            set @has_rolled_back = 1;
        end
        else if @xact_state = 0
        begin
            -- transaction was already rolled back (deadlock?)
            set @has_rolled_back = 1;
        end
        insert into dbm_notifications_errors(
            incident_time,
            session_id,
            has_rolled_back,
            [error_number],
            [error_message],
            [message_body])
        values (
            getutcdate(),
            @@spid,
            @has_rolled_back,
            @error_number,
            @error_message,
            @raw_body);
        if (@has_rolled_back = 0)
        begin
            commit;
        end
    end catch
end
go

Writing service broker procedure is not your run-of-the-mill code. One must follow certain standards and is very easy to stray off into quicksand territory. This code shows some good practices:

wrap the message dequeue and the processing in a transaction. No brainer, obvious.
always check the message type received. A good service broker procedure must handle Error and EndDialog messages appropriately by ending the dialog from it's side. Not doing so results in handle leaks (sys.conversation_endpoints grows)
always check if a message was dequeued by RECEIVE. Some samples check@@rowcount after RECEIVE, which is perfectly OK. This sample code relies on the message name check (no message implies NULL message type name) and handles that case implicitly.
create a processing errors table. the background nature of SSB activated procedures makes it really difficult to troubleshoot errors if the messages simply vanish w/o a trace.

Besides, this code also does some good-practice code with regard to the task at hand (monitoring DBM):

differentiate between post_time (when was the notification sent?), start_time (when did the action that triggered the notificaton start?) and processing_time (when was the notification processed?). post_time and start_time will likely be identical or very close, but processing_time can be seconds, hours, days apart from post_time. the interesting one for audit is usually post_time.
since the post_time and the processing_time are different, it should be obvious that a DBM monitoring task in an even notification activated procedure has no business looking at sys.database_mirroring view.  That view will show the current state at the moment of processing, which may or may not be related to the event. If the processing occurs a long time after the event is posted (think maintenance downtime) than the issue is obvious, but it can handle also in 'healthy' processing if the DBM changes state very fast and post two (or more) events in a row (which happens frequently): in this situation the processing, as in the code you posted, audit the event as they occur, but will record the current, final, state. Reading such an audit could be very confusing later.
always audit the original XML event. This way you can later query this XML for any information that was not 'shredded' into columns in the audit table.

Step 3: attach the procedure to the queue:
alter queue dbm_notifications_queue
with activation (
    status=on,
    procedure_name = [dbm_notifications_procedure],
    max_queue_readers = 1,
    execute as  owner);

Repeating the above steps on all instances involved in mirroring ensures you get notified no matter which instance is the principal.


Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way Consider a CLR stored procedure that accepts an XML parameter and calls another stored procedure in the same schema:

[SqlFunction(DataAccess = DataAccessKind.Read,
     IsDeterministic = false, IsPrecise = true,
     SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlBoolean TestTest(SqlXml Data)
{
    using (var c = new SqlConnection("context connection=true"))
    {
        c.Open();

        using (var cmd = new SqlCommand(@"select case when exists(select 0 from [testing].WillBeCalledByCLR(@d)) then 1 else 0 end;", c))
        {
            var p = cmd.Parameters.Add("@d", SqlDbType.Xml);

            p.Direction = ParameterDirection.Input;
            if (Data.IsNull)
                { p.Value = DBNull.Value; }
            else
                { p.Value = Data; }  // Or Data.Value

            return (bool)cmd.ExecuteScalar();
        }
    }
}

This CLR function is visible in SQL as:

create function [testing].[TestTest] ( @data xml )
returns bit
WITH CALLED ON NULL INPUT
AS
EXTERNAL NAME [Test].[Test.Test].[TestTest]

The stored function it calls is this:

create function testing.WillBeCalledByCLR (@x xml = null)
returns table
as
return (
  select 1 as one, 2 as two, 'three' as three
);

In this setup, if I call the CLR function like this:

if testing.TestTest(null) = 1
begin
  select 'Meaningful actions';
end;

or like this:

declare @res bit = testing.TestTest(null);

or like this:

declare @res bit;
set @res = testing.TestTest(null);

or like this:

declare @res bit;
select @res = testing.TestTest(null);

then I get:

  Msg 2905, Level 25, State 1, Line 6

  Msg 0, Level 20, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

But if I call it like this:

select testing.TestTest(null);

or like this:

declare @res bit;
set @res = (select testing.TestTest(null));

I get a proper value back (e.g. 1).

If, instead of null, I pass an empty string '', the function is successfully called in all cases.

Why? Did I do something wrong in my CLR function?

  Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
  Sep 16 2010 19:43:16
  Copyright (c) 1988-2008 Microsoft Corporation
  Standard Edition (64-bit) on Windows NT 6.0  (Build 6002: Service Pack 2) (VM)

Overall, no, you did not do anything wrong with your .NET code. There is a minor technical issue, but we will get to that in a moment.

I was able to reproduce this (both error and non-error scenarios) in SQL Server 2008 R2 RTM. At first, in the "non-error" scenario, I was getting another error for:

  specified cast is invalid

This error was a result of the query passing back an INT and the C# code attempting to cast it to a bool. I changed the query to be:

... then CONVERT(BIT, 1) else CONVERT(BIT, 0) end ...

That forces the datatype of the value to be returned to be something that can be cast into bool. This was the "minor error" that I was referring to, and I consider it "minor" and not really an error because I suspect your code originally attempted to cast into int and returned a SqlInt32 since you claim that passing in an empty string allowed it to return successfully. There is no way this would ever return successfully, even with an empty string instead of NULL for the input value, if your code was attempting to cast to bool and return SqlBoolean. And yes, I could have changed the cast to be (int) and the return type to be SqlInt32, but this was less of a structural change.

I tested several types of changes: removing the execution, removing the @d parameter and hard-coding the null into the query but still having the SqlParameter defined, removing the @d parameter and the SqlParameter, etc. What it finally came down to is this: merely the existence of a SqlParameter of a LOB type (i.e. XML, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) ), even if unused in the query, caused the "severe error" when passing in a NULL. However, if any of those MAX types were changed to be their maximum non-MAX lengths (i.e. 8000, 4000, and 8000 respectively), then there was no error when passing in a NULL. Hence the following reduced code should be enough to reproduce the error when passing in a NULL to the SQLCLR scalar function:

using (var cmd = new SqlCommand(@"SELECT 1;", c))
{
    var p = cmd.Parameters.Add("@d", SqlDbType.NVarChar, (int)SqlMetaData.Max);

    cmd.ExecuteScalar();
}

Now, I was not able to reproduce the error with the original code in SQL Server 2012 SP3 or SQL Server 2014 SP1 :-). Therefore, I suspect that this was an issue with CLR 2.0 and/or the .NET Framework versions associated with CLR 2.0 (i.e. "2.0", "3.0", and "3.5"). OR, another aspect to consider is that it with this failing ( declare @res bit; set @res = testing.TestTest(null); ) while this works ( declare @res bit; set @res = (select testing.TestTest(null)); ) that could point to a problem with the Query Optimizer and not with the CLR version. Or maybe it is a combination of the two. Or perhaps it is Descartes's pesky Evil Demon. Either way. ( ** please see comment at the end )

If you are able to move up to SQL Server 2012 or newer, then the problem should "magically" disappear. BUT, if stuck on SQL Server 2008 / 2008 R2, I was able to work around the issue by declaring the parameter to be a non-MAX type if the input is NULL, which should be fine since there won't be any risk of truncation when passing through a NULL. And, in the case of the input parameter datatype of the T-SQL object being called being XML, it still works to define the parameter datatype in the .NET code as NVARCHAR(4000) as it will implicitly convert that into XML when called. The following code worked for me in SQL Server 2008 R2 when passing in a NULL:

SqlParameter _XmlData;

if (Data.IsNull)
{
    // Parameter can't be a LOB type if input is NULL
    _XmlData = new SqlParameter("@d", SqlDbType.NVarChar, 4000);
    _XmlData.Value = DBNull.Value;
}
else
{
    _XmlData = new SqlParameter("@d", SqlDbType.Xml);
    _XmlData.Value = Data.Value;
}

cmd.Parameters.Add(_XmlData);

** @MartinSmith left this illuminating comment:

  Specifically the error seems to be "Access violation reading location 0x0000000000000000" inside sqlservr.exe!CClrXvarProxy::EnsureBufferInit so looks like a null pointer bug but googling those method names doesn't return any KB article.


How does Postgres handle integer data of different sizes in the same column? If I have a bigint column and in one row store 1 and in another store 999999999999, will these take up different amounts of space on disk?

Will Postgres have an easier time doing queries and calculations with the smaller data?

The motivation for my question is that I have a lot of data in this form: 1000000306429071. I'm wondering if it will be valuable to strip the 1 and leading 0s.

If I have a bigint column and in one row store 1 and in another store 999999999999, will these take up different amounts of space on disk?

Bigint is always 8 bytes regardless of what the decimal representation is

1            | 0000000000000000000000000000000000000000000000000000000000000001
999999999999 | 0000000000000000000000001110100011010100101001010000111111111111

  Will Postgres have an easier time doing queries and calculations with the smaller data?

Yes. int4 takes half the amount of space as int8. That means at most you'll save 4 bytes per tuple in the row, and the index will be half the size.

  The motivation for my question is that I have a lot of data in this form: 1000000306429071. I'm wondering if it will be valuable to strip the 1 and leading 0s.

That sounds like a wise idea to me. The max value for an int4, is 2147483647, putting them next to each other.

2147483647
306429071

You can see that you're not even close to exhausting that range. You're only 14% there. Do you ever expect to need more than 2147483647? If so, you definitely want to stay with int8.

You can return the value with..

1e15::bigint + 306429071


What is the best practice for mapping from natural keys to integer-based keys? (ETL) This question originates more from an "enterprise architecture" point of view. (Reason being, our company is just starting to get into data management, and actually having DBA's -- I know, horrific that it took so long to get to data management and having DBA's, but bear with me).

I'll lay some background first.

I am researching on how to move data from a legacy system to the new replacement system we are building. This data is more "lookup" data than what i would call "business", "customer", or "domain" data (if that makes sense). I guess the way I think of it is this is data that provides end-user's a choice and is then associated to the other types of data.

The challenge is that until we roll off our legacy systems we don't want to maintain the (legacy) data in two systems. We will need to maintain new data that can't work with the legacy system in the new system (so in that sense we will have dual maintenance). But the data that is common we want to initially push or pull from the legacy system.

This means some form of ETL, whether that be manually coded, or whether we use a product.
(From a code perspective that means for us either RPG-since the legacy system is iSeries and DB2 for i-or Java-since the new system is Java EE-based with DB2 on AIX). From a product standpoint that probably means IBM InfoSphere DataStage (as that is product we have chosen to use for ETL for warehousing).

Now to the actual point of my question: The legacy system has data in non-normalized tables that rely on natural keys. The new system is in normalized tables that look more "object oriented" in their design (because of the heavy use of Hibernate). These tables rely more on the traditional integer-based primary key.

I am wondering, how does one best map from a system with natural keys to one with integer-based keys? How do you maintain this mapping so you know when to update records and when to insert new records into the integer-based system.

Any recommendations or best practices in general?

And then as an add on: Anything specific to RPG, Java, or InfoSphere DataStage?

Any ideas are appreciated, as these may be incorporated into our company's best practices we are attempting to build.

I have found building a relationship table between the old (natural) and new (integer) keys works well in conversion processes such as this.  The integer key is built using the standard auto-increment method for the database.  Maintain unique keys on the data which forms the natural key in the new tables.

Develop a repeatable data synchronization method that can be applied as often as required.  Depending on your needs this can be daily, weekly, monthly, or on-demand.  Make the synchronization modular so it can be updated as required.  In some development processes you may end up feeding from the new system back to the old system.

As you develop and deploy the system, track which system is the system of record for which data.  Move control to the new system as possible.  You may want or need processes to identify and deal with changes which occur on the system which is not the system of record.

Manage change overs from the old system to the new.  If possible, disable updates in the old system as the system of record is changed to the new system.  This will require a feed from the new system to the old.


OLTP - Stretch database vs linked server From my understanding, when you enable the StretchDB feature for a table, basically creates a Linked Server locally, which has a remote server as a target to move data to.

If this is correct, does this mean that they will have the same problems? Such as:

Getting all the data and then filtering after
Terrible statistics on the data

Kendra Little has a Youtube video on Brent Ozar's website outlining some of these problem.

Limitations for Stretch Database

Not all tables will be eligible for being Stretch-enabled. Certain table properties, data and column types, constraints, and indexes are not supported, such as:

Memory-optimized and replicated tables
Tables that contain FILESTREAM data, use Change Tracking or Change Data
Data types such as timestamp, sql_variant, XML, geography or columns that are Always Encrypted
Check and default constraints or foreign key constraints that reference the table
XML, full-text, spatial, clustered columnstore and indexed views that reference the Stretch-enabled table
You cannot run UPDATE or DELETE statements, or run CREATE INDEX or ALTER INDEX operations on a Stretch-enabled table


Where did these dbo._trace_ Stored Procedures come from? I'm looking at some databases at a new company and one in particular has a bunch of Stored Procedures named dbo._trace_*

SQL Default Trace is ON, but I'm not sure if these are related, and if they were, I don't know why I would see them in one database on the server but not in other databases on that server.

Look at the stored procedure contents.
It they begin like the following example, they could be server side traces someone has created.

Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100 Create the trace
exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace’, @maxfilesize, NULL
if (@rc != 0) goto error --Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on


Need to list SQL Windows auth users on each SQL server and rename the domain name We are doing an AD Domain NetBIOS rename. We have about 20 SQL servers, and on each we have a series of logins like OLD.DOM\sqluser1 etc. I want to do two things, and a third would be a bonus. I'm not sure how to put these together:

SELECT * FROM Sys.login_token

I think this will list the users I mentioned above? I'd like to put that into a variable and then run this I think:

ALTER LOGIN "OLD.DOM\variable_holding_username"
WITH NAME="NEWDOM\variable_holding_username"

So if I can go on a server, list out all of those OLD.DOM\ logins and ALTER each of them, I'd be happy.

I'd be extra happy if I could hit each SQL server remotely and run through this, instead of doing each one separately.

Unless you are actually deleting the user accounts in Active Directory and re-creating them, you don't actually need to do anything to the logins on SQL Server.

SQL Server references the accounts using the Active Directory SID, not the name.

You can test this by renaming an account in Active Directory and attempting to login to SQL Server with it.

Assuming you are using at least a semi-modern version1 of SQL Server, you can use ALTER LOGIN [DOMAIN\xyz] WITH NAME = [NEWDOMAIN\xyz]; syntax to modify the name of a Windows Active Directory login in SQL Server.  You can use the system DMV, sys.server_principals to determine which logins should be modified.  For example:

DECLARE @OldDomain SYSNAME;
DECLARE @NewDomain SYSNAME;
DECLARE @cmd NVARCHAR(MAX);

SET @OldDomain = 'MyOldDomain';
SET @NewDomain = 'NewDomainName';

DECLARE cur CURSOR LOCAL FORWARD_ONLY
FOR
SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) +
    ' WITH NAME = ' + QUOTENAME(@NewDomain +
    RIGHT(sp.name, CHARINDEX('\', REVERSE(sp.name)))) + ';'
FROM master.sys.server_principals sp
WHERE sp.type_desc LIKE 'WINDOWS_%'
    AND sp.name LIKE @OldDomain + '\%';

OPEN cur;

FETCH NEXT FROM cur
INTO @cmd;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT (@cmd);

    FETCH NEXT FROM cur
    INTO @cmd;
END

CLOSE cur;
DEALLOCATE cur;

The above code will print a list of commands that could be executed to modify the logins for Windows User Accounts and Windows Groups.

You can run the above code against multiple servers by creating a group of servers in the Server Registration Window in SSMS, the right-clicking it and selecting "New Query", as shown here:

This allows you to run any query against multiple servers simultaneously, and see the results in one window.  The results have a "Server Name" column added automatically so you determine where each row came from.

1 - SQL Server 2008+


Do data checksums apply to large objects? PostgreSQL 9.3 introduced the data checksums feature (initdb -k). Do these checksums apply to large objects?

If enabled, checksums will be calculated for each data page.
you can read more about check some  in the following site: http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-data-checksums/

or the following website also tell about checksum http://www.postgresql.org/docs/9.3/static/app-initdb.html#APP-INITDB-DATA-CHECKSUMS
Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. Enabling checksums may incur a noticeable performance penalty. This option can only be set during initialization, and cannot be changed later. If set, checksums are calculated for all objects, in all databases.


how to select a value in one table with max() to update a row in a second table, with a trigger in PostgreSQL Here is tbl1 :
gid pm
1   2.1
2   2.0
3   1.95
...

Here is tbl2 I want :
gid num pb  gidTbl1
1    1        1
2    2        1
3    3  2.1   1
4    1        2
5    2        2
6    3        2
7    4  2.0   2
8    1        3
...

On an insert in tbl2, I would like that pb = pm for gidTbl1 = gid AND for the max num in tbl2.
Here are a trigger and the function on tbl2 I wrote :
CREATE OR REPLACE FUNCTION maj_pb()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
IF TG_OP IN ('INSERT')
THEN NEW.pb = pm from tbl1
        where NEW.gidTbl1 = tbl1.gid
        and num = (select max(num) from tbl2
                          where NEW.gidTbl1 = g.gid
                        ) ;
RETURN NEW ;
END IF ;
END ;
$BODY$;

CREATE TRIGGER trg_maj_pb
BEFORE INSERT
ON tbl2
FOR EACH ROW
EXECUTE PROCEDURE activite.maj_pb();

error message is :

num doesn't exist

I presume that the trigger is looking for num in tbl1 (?). But I can't find an other way to write the function.

I add : In fact, whatever I do (insert or update) the max num by gidTbl1 (tbl2) always has to have the pm corresponding in tbl1.

The immediate error is that you have num where it would have to be NEW.num. But there are more problems ...

I add : In fact, whatever I do (insert or update) the max num by gidTbl1 (tbl2) always has to have the pm corresponding in tbl1.

Trying to get this right with triggers is a pain, even without possible concurrent writes.
With possible concurrent writes, it's only even possible with expensive locks (or with SERIALIZABLE snapshot isolation). You can't have multiple concurrent transactions messing with the max num at the same time.
You could force all write access on tbl2 to first take out a row lock on the respective parent row in tbl1 (and keep track of the current max tbl2.num in an additional column tbl1.max_num while being at it) ...
See:

UPDATE n:m relation in view as array (operations)
Delete parent if it's not referenced by any other child

But I wouldn't go there. The safe way to achieve this is to drop the column tbl2.pb altogether. Instead use a VIEW (or MATERIALIZED VIEW) like this to dynamically get the current value, where you need it:
CREATE VIEW tbl2_plus_pb AS
SELECT t2.gid, t2.num, t1.pm AS pb, t2.gid_tbl1
FROM  (
   SELECT *, CASE WHEN max(num) OVER (PARTITION BY gid_tbl1) = num
                  THEN gid_tbl1 END AS link
   FROM   tbl2
   ) t2
LEFT   JOIN tbl1 t1 ON t1.gid = t2.link
ORDER  BY t2.gid;

db<>fiddle here


How do automaticly delete rows from other tables I have a MySql DB.

I have a table - recipes.
Also have 2 tables -ingredients and instructions. both have for each entry - a foreign key to the related recipe.

I want that when I delete an entry from the recipes table - to automaticly delete the related ingredients entries and instruction entries.

How?

Whatever you are trying to do can easily be done by

USING ON DELETE CASCADE AND ON UPDATE CASCADE(IF Required)

While creating the foreign keys on your table.

For your reference have a look at FOREIGN KEY Constraints.


Is there any way to revoke drop permission from table in oracle as admin? I tried this -

revoke drop on table emp from user1;

but it is not working, error is

Error report -
SQL Error: ORA-00990: missing or invalid privilege
00990. 00000 -  "missing or invalid privilege"

i looked up on oracle documentation and it had only 4 or 5 privileges like select, insert, delete, update but not drop.

There is no drop table privilege.

Users always have full permissions on their own objects, even if they didn't create those objects.

sqlplus kjohnston

SQL> create user testuser;
SQL> grant create session to testuser;
SQL> create table testuser.testtab (x varchar2(1));
SQL> exit;

sqlplus testuser
SQL> drop table testtab;
table dropped

However, there is drop any table privilege for dropping tables in other schemas:

sqlplus kjohnston
SQL> grant create any table to testuser;
SQL> exit;

sqlplus testuser
SQL> create table kjohnston.testtab (x varchar2(1));
table created
SQL> drop table kjohnston.testtab;
ORA-01031: insufficient privileges
SQL> exit;

sqlplus kjohnston
SQL> grant drop any table to testuser;
SQL> exit;

sqlplus testuser;
SQL> drop table kjohnston.testtab;
table dropped

For this reason, it is considered best practice to separate accounts that will be logging in to the database (actual users, website logins, etc...) from users that own the objects (in which case, consider not even granting create session privilege to these users so that they can't login to the database).


How can I do the following task in Oracle I wish to declare a variable, and create a temp table, and insert some data into the table, and show the data.  BTW, I don't what this done in a new stored procedure:

DECLARE
   I NUMBER := 1;

begin
  CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);
  WHILE I <= 10 LOOP
    INSERT INTO temp Values (I);
    I := I + 1;
  END LOOP;
end;
SELECT * FROM temp;
/

But it has a compiling error:

  ORA-06550: line 5, column 7: PLS-00103: Encountered the symbol
  "CREATE" when expecting one of the following: ( begin case declare
  exit for goto if loop mod null pragma raise return select update while
  with    << continue close current delete fetch lock insert open
  rollback savepoint set sql execute commit forall merge pipe purge

Thank you Marco. I change it to the following but still not working:

DECLARE
   I NUMBER := 1;
   global temporary table temp(CID NUMBER) on commit preserve rows;
begin
  WHILE I <= 10 LOOP
    INSERT INTO temp Values (I);
    I := I + 1;
  END LOOP;
  SELECT * FROM temp;
end;
/

ORA-06550: line 3, column 25: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; not null range default character

The following code does not work either.  It looks I cannot place declare and create in the same code:

CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);
DECLARE
   I NUMBER := 1;
begin
  WHILE I <= 10 LOOP
    INSERT INTO temp Values (I);
    I := I + 1;
  END LOOP;
  SELECT * FROM temp;
end;
/

The error message: ORA-00911: invalid character

You should probably consider creating the Global Temp table once ... outside of this entire process ...
creating it "on the fly" like this doesn't really make any sense.

Note also that the data is by default transaction based ... so those records you're inserting will tend to "dissappear" quite quickly (ie once you hit a commit).

  SQL> CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);

  Table created.

  SQL> insert into temp ( select level from dual connect by level <= 10);

  10 rows created.

  SQL> Select * from temp;

         CID
  ----------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

  10 rows selected.

  SQL> commit;

  Commit complete.

  SQL> Select * from temp;

  no rows selected

  SQL>

You don't need the PL/SQL block for this, and in fact creates more overhead than it's worth.


No data in Azure SQL after importing bacpac file I've created a bacpac file of my database for transfer to Azure. The file is uploaded and imported successfully but there's no data in the imported database. What did I do wrong? There don't seem to be options about with or without data. The structure did end up correctly in the database.

I verified the bacpac by importing it locally.

TL;DR: It wasn't finished importing yet.

I found out - it was impatience. As soon as I could access the DB and see the structure I thought the import process was done. But it turned out it was still importing. So good to know that you can access it before it's ready. (In hindsight there was sort of an indicator, while it was still importing it showed 1 additional table, something with Transaction... bla).

However, I could have known as I found out that there would still be an 'Importing...' item in the Notifications panel in the Azure portal during the import. It doesn't show that on any of the blades however.


Update from PHP 5 to PHP 7.0 - PHPmyAdmin will show two server logins: localhost / localhost:3306 I updated my Linux 14.04 LTS to 18.04 and had to downgrade the PHP 7.2 to 7.0 as I need to run some software which is not capable of the preinstalled PHP 7.2. I used PHP 5 so far.

After I got everything runnning now, MySQL, Apache and PHP I created a new database and now I get a Notification that the control user can't be connected and that 'pmp'@localhost has denied access.

This appears when I log into server @ localhost.

If I choose localhost:3306 everything works fine. But I do not know why I have two server logins?

I'm somehow confused, that I get two servers to choose from (localhost and localhost:3066) which I would not have expected. Can I somehow delete the localhost-access for PHPmyAdmin and only enable localhost:3306?

Any idea what I have to do? I'm a database newbie. Best

There are two ways to connect to the mysql on the localhost. One is the connection via network @localhost:3306 and another is the connection through the filesocket like /tmp/mysql.sock. Filesocket connection is preferred due to the lower overhead and mysql library choose it if available when connection to the localhost requested. I have no clue why they are separated now.

If you want to connect to the mysql only locally then networking can be disabled completely in the my.cnf so all connections are forced to be filesocket-based. That is also a bit more secure as far as no one can connect to the mysql over the network.


Count is slow Oracle this is my query :

SELECT Count(last_upd)
FROM S_CONTACT
WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd  +  1/24 > SYSDATE - (1 / (24 * 60));

It takes 84 seconds to return me this result :

COUNT(LAST_UPD)
---------------
43

I don't understand why it's too much slow for only 43 returned row...

This is the execution plan :

Can anyone help me? this is the V$STATNAME

active txn count during cleanout    1
bytes received via SQL*Net from client  497
bytes sent via SQL*Net to client    14803
calls to get snapshot scn: kcmgss   23
calls to kcmgas 45
cleanout - number of ktugct calls   12
cleanouts and rollbacks - consistent read gets  2
cleanouts only - consistent read gets   10
commit txn count during cleanout    11
consistent changes  450
consistent gets 1458915
consistent gets - examination   458
consistent gets from cache  1458915
CPU used by this session    4485
CPU used when call started  4493
CR blocks created   44
current blocks converted for CR 1
cursor authentications  1
data blocks consistent reads - undo records applied 59
db block changes    12
DB time 8383
dirty buffers inspected 31
enqueue releases    1
enqueue requests    1
execute count   2
free buffer inspected   1442014
free buffer requested   1436612
heap block compress 12
hot buffers moved to head of LRU    25764
immediate (CR) block cleanout applications  12
no work - consistent read gets  1458380
opened cursors cumulative   2
OS Involuntary context switches 18069
OS Maximum resident set size    1956
OS Page faults  4
OS Page reclaims    410
OS System time used 1868
OS User time used   2625
OS Voluntary context switches   63594
parse count (hard)  1
parse count (total) 2
physical read bytes 11768365056
physical read IO requests   55724
physical read total bytes   11768365056
physical read total IO requests 55724
physical read total multi block requests    54675
physical reads  1436568
physical reads cache    1436568
physical reads cache prefetch   1380844
prefetched blocks aged out before use   8
recursive calls 1
redo entries    12
redo size   1272
redo subscn max counts  8
rollbacks only - consistent read gets   40
session logical reads   1458915
session pga memory  -393216
session pga memory max  262144
session uga memory max  299256
shared hash latch upgrades - no wait    4
sorts (memory)  2
sorts (rows)    760
SQL*Net roundtrips to/from client   9
table scan blocks gotten    1458432
table scan rows gotten  16656961
table scans (long tables)   1
transaction tables consistent read rollbacks    4
transaction tables consistent reads - undo records applied  391
user calls  11
user I/O wait time  6172
workarea executions - optimal   5

I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bits

Thank you

the query does not return 43 rows but 1 row that contains the number 43
the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects.
from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table (table scan rows gotten 16656961).
for this it has to read about 11G data (physical read total bytes 11768365056) and executes 55,000 read operations (physical read total IO requests 55724). 55,000 reads in 60 seconds (user I/O wait time 6172) means 1.1 ms per read. This is a good value.

You have to decrease the number of row your query has to process. An index may help Oracle to count efficiently the number of rowa you are interested in.

The most selective subclause of your where-clause will be the last_upd subclause. So make an index on this column.
You should add the columns from the other where-clauses (and the select-clause) to the index, too. So Oracle does not have to lookup each fitting row in that is found by the index in the table to get the last_upd_by and last_name values.
The last_upd_by subclause is the next selective clause so it schould be the next column in the index. So you should make an inex on the columns (last_upd, last_upd_by, last_name) in this order.


Is it possible to CREATE EVENT to another server? I have serverA and ServerB. I need ServerA to copy a database to ServerB and UPDATE/INSERT/DELETE as it changes. I think this can be done with replication but I don't want to continually open a connection. Is it possible to use CREATE EVENT or TRIGGERS? If so, where do I start?

Replication DOES NOT require an open connection. There's even an entry in the FAQ for this.

B.13.1: Must the slave be connected to the master all the time?
No, it does not. The slave can go down or stay disconnected for hours or even days, and then reconnect and catch up on updates.

If anything, replication is the most efficient solution when you only want to send changes from a master server to a replica periodically


Trigger performance - UPDATE statement joining on INSERTED I have a trigger (MS SQL Server) on TABLE_A that fires on an update that looks basically like the following (I've changed the names of the tables/trigger to simplify):

CREATE TRIGGER [dbo].[TABLE_A_B_UPDATE] ON [dbo].[TABLE_A]
FOR UPDATE
AS

IF UPDATE ( [ATTRIBUTES] )
BEGIN
    BEGIN TRAN
    -- update the attributes of the table C records
    UPDATE [TABLE_C]
    SET [ATTRIBUTES] = I.ATTRIBUTES
    FROM TABLE_C C
    INNER JOIN INSERTED I ON
        C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID
    WHERE E.[ATTRIBUTES] & 4 = 0

    UPDATE  [TABLE_B]
    SET     [ATTRIBUTES] = I.ATTRIBUTES
    FROM TABLE_C C
    INNER JOIN INSERTED I ON
        C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID
    INNER JOIN [TABLE_B] B ON
        B.UNIQUE_ID = C.UNIQUE_ID AND B.USER_ID = C.USER_ID
    WHERE B.[ATTRIBUTES] & 4 = 0

    COMMIT TRAN
END

Currently by design, there is only ever ONE record in the INSERTED table (we are only updating one record at a time from a UI).

What we have discovered is that, as the number of records increase in TABLE_B, trigger performance degrades rapidly. For example, with around 12000 or so records in TABLE_B, this update statement takes around 40 seconds (we established a timeout of 30 seconds). As I remove records from TABLE_B, performance gradually improves. As this was an unacceptable solution, I had to find ways to improve this update statement.

Through testing/profiling, I found that the problem was with the second update statement (update TABLE_B). The first update statement works without problem; if I change the second update statement to its equivalent SELECT statement, it also runs fast.

The solution that I found was to shove the singular record in the INSERTED table into a #TEMP table and join on that instead. I was also able to do this with a table variable as well, but performance was terrible until I created an index on it. This immediately resolved the problem and the update now runs almost instantaneously.

My question is this - why did this solve the performance problem? Perhaps I am looking at this in the wrong way, but I can't imagine why I would need an index on a one record table. I have read that the INSERTED table isn't created with an index on it, but it still seems odd to me that I should need one.

Thanks in advance!

EDIT: As pointed out, I forgot to mention some other relevant table structure tidbits.

TABLE_B indeed has a compound primary key/index created as follows:

CONSTRAINT [TABLE_B] PRIMARY KEY CLUSTERED
(
    [USER_ID] ASC,
    [UNIQUE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

TABLE_C has the same index as above. All indexes were rebuilt at the start of testing.

Both tables have additional triggers that are being fired - however, during testing, I disabled these triggers to determine where specifically the performance hit was. Disabling all other triggers did not improve performance.

EXECUTION PLAN:
I'm not super savvy on execution plans for triggers, but as far as I can tell, you can view them from the profiler with the showplan option turned on. I believe this is the relevant plan:

I'm not sure what to think about this plan, other than I noticed that 80% on that clustered index scan. Hopefully this is what was being asked for, if not, I can repost. Thanks again!

as the number of records increase in TABLE_B, trigger performance degrades rapidly

That would imply the updates do not have appropriate indexes available, or are unable to use them for some reason, on TABLE_B or the tables that it is joined to.

Without more detail about the table layout (what indexes you have on each of the tables) and the query plan that SQL has decided to use we can't help you in much detail, so I suggest you update the question with that information.

Some first thoughts:

  FROM TABLE_C C INNER JOIN INSERTED I ON C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID

As you say the performance is tied to TABLE_B this is probably OK, though again the query plan would be helpful information to provide. Presumably you have an index on TABLE_C.UNIQUE_ID or it is the primary key or subject to a unique constraint.

  INNER JOIN [TABLE_B] B ON B.UNIQUE_ID = C.UNIQUE_ID AND B.USER_ID = C.USER_ID

With the right indexes/keys this should not be an issue either. For maximum efficient for this specific statement TABLE_C should have an index covering UNIQUE_ID & USER_ID and TABLE_B one covering UNIQUE_ID & USER_ID and including ATTRIBUTES.

  WHERE B.[ATTRIBUTES] & 4 = 0

This may be significant. Depending on how TABLE_B and TABLE_C are getting joined (again, provide the index details and query plan) this could be forcing a table scan of TABLE_B. The expense of that operation would grow at linearly with the number of rows in TABLE_B.


Amazon RDS non superuser create function in C I am asked to restore a Rails application to a postgresql RDS instance. There are many functions in C but looks like only superuser can create functions in C and I could not get superuser access on RDS instance, so I am kind of stuck.

any suggestions ?

The Amazon RDS is marketed as

  [...] managing time-consuming database management tasks, freeing you
  up to focus on your applications and business.

From one point of view, this is true, it relieves you from a lot of maintenance.  On the other hand, it means less flexibility, in our case the lack of access to the filesystem.

The PostgreSQL documentation states:

  The CREATE FUNCTION for a user-defined C function must therefore
  specify two pieces of information for the function: the name of the
  loadable object file, and the C name [...]

As you cannot put files onto the machine, this simply won't work.

If you have to do your work on some AWS platform, you can choose an EC2 instance, which you can basically handle like any other machine you have root access to.


Reporting services - High availability setup - SQL server 2012 I need some guidance on best practices and setup for SSRS. I have a SQLServer 2012 setup with data source on 1 instance and SSRS on a separate server. There is a need to setup HA and I understand what is involved with the setup for the data source server. This is a large instance with many databases.

The SSRS instance is very small (350 MB). Should I setup AOAG for the report server db? OR configure a scale-out deployment? The goal is for smooth transition should one server go down. I am not very familiar with SSRS i.e setup beyond report db - the report manager etc. Would failover cluster manager take care of the rest. I will have to guide the application team with this.

Getting some input as to the direction I need to look and research will be gratefully appreciated.

Thank you,
Debbie.

Solutions that provide HA will serve you well - both a failover cluster instance or AOAG. Technet articles are great to help you with this and understand it deeper.

For instance, if you use a FCI (SQL Server failover cluster instance), while installing you provide a network name which identifies the instance. Your SSRS only needs to know this networkname\instancename. Then, when the primary node failsover, another node will assume the work and keep serving your SSRS with the databases. Do notice that this solution undergoes a small downtime - the time for the cluster to disconnect the disks from the prior primary node to the "new to be" primary node, while transfering the cluster groups.

If you use AOAG, your SSRS will have to work with the VNN (virtual network name), and, when a failover occurs, a secondary node will assume the work and keep providing data. As in this case the node has its own disks (providing a second copy of the data, while on FCI you just had one copy of the data), the failover doesn't face the "transfer the disks" downtime.

As for the report server itself, taking into account your comment, this pages will provide you great guidance to scale-out:

https://msdn.microsoft.com/en-us/library/bb522745.aspx

https://msdn.microsoft.com/en-us/library/ms159114.aspx

You may want to consider setting up a NLB to take advantage of having more than one SSRS server. This should be ocnfigured first, and you can read about it here:

https://msdn.microsoft.com/en-us/library/cc281307.aspx

And, just in case, some aditional information for Reporting Services with AlwaysOn Availability Groups:

https://msdn.microsoft.com/en-us/library/hh882437.aspx


Getting ratio of reads to writes on SQL Server Sorry I am not a DBA so bear with me.  I wanted to get a better understanding of the ratio of reads to writes in our archiecture.  The best to get this is at the database. We are using SQL Server 2008.  Is there anyway to get this without impacting performance and to get smart summaries?  For example, the daily read / write ratio?

Many thanks.

This will be incomplete, but it will give you a good idea of the ratios you're looking for. You can use sys.dm_db_index_usage_stats to retreive the access from various indexes on the system. If you combine the seeks, scans, and lookups you'll get a good idea of the reads. Something like this:

SELECT  ddius.user_updates,
        ddius.user_lookups + ddius.user_scans + ddius.user_seeks AS user_reads
FROM    sys.dm_db_index_usage_stats AS ddius

This data is cumulative, so you'll need to capture the previous day in order to find the differences between yesterday & today. As I say, this won't give you a perfect measure, but it will move you in the right direction.


Can SQL Server use a spatial index and another index at the same time and be efficient? Let's say we have a table that contains two terrain datasets defined by TerrainModelID like below:

  CREATE TABLE [dbo].[DigitalTerrain](
  [DigitalTerrainID] [uniqueidentifier] NOT NULL,
  [Longitude] [float] NULL,
  [Latitude] [float] NULL,
  [ZCoordinate] [float] NULL,
  [Tile] [nvarchar](10) NULL,
  [TerrainModelID] [uniqueidentifier] NULL,
  [Location] [geography] NULL,
  CONSTRAINT [PK__DigitalTerrainID] PRIMARY KEY CLUSTERED(DigitalTerrainID))

I have a spatial index, Location_IX and also an index on the TerrainModelID column. In a query like the below is having the two datasets in the same table going to cause the spatial index issues/slowdowns?

SELECT
    Location,
    ZCoordinate,
    TerrainModelID
FROM TerrainData.dbo.DigitalTerrain WITH(INDEX(Location_IX) NOLOCK)
WHERE @line.STDistance(DigitalTerrain.Location) < (@margin) AND TerrainModelID = @TerModID

Is it still able to filter out the terrain model dataset we want and use the spatial index efficiently? Or should we differ the design or query here? Should this have been split into two tables, one for each terrain dataset?

mickyt: With only 2 models in your dataset the spatial index will be more effective than an index on model id.  It will still do an index seek on the model id if required.  However if you start adding more and more models to the table, you may get to a tipping point.

I tested on a table with 1,000,000 points and 99 models.  Using the model index only was quicker and more efficient than forcing the spatial.  With 9 models over the 1,000,000, forcing the spatial index was quicker and more efficient.


How to manage two tables in SQL Server i have two tables offer1 and offer2

The two tables are

Offer1
Offer2ID|OfferName|OfferExpiryDate

Offer2
Offer2ID|OfferName|OfferExpiryDate

now i have third table which keeps record

offersentdetail
offerID|senttime|sentCount|UserID

now i want output sent on Specifc userID

Output will be like

UserID | OfferID | OfferName

Try this:

select osd.UserId, osd.OfferId, ou.OfferName
  from dbo.offersentdetail as osd
    inner join (
      select OfferId = Offer1Id, OfferName, OfferExpiryDate
        from dbo.[Offer1]
      union all
      select OfferId = Offer2Id, OfferName, OfferExpiryDate
        from dbo.[Offer2]
        ) as ou
    on osd.OfferId = ou.OfferId


100 TeraBytes Capacity Database - Resources and Time Estimates I am working on a 'back of the envelope' calculation for a 100TB reporting database setup. I am seeking out thoughts from the experts here. Proposed environment:

Storage Capacity ~ 100TB
Tables ~ 200, sizes ranging from 1GB to 5TB. mean size could lie between 100GB-200GB
ETL - jobs may require join between tables of 10's of millions of rows, with join keys ranging from 10 bytes to 500 bytes. such joins should finish in under 2-5 minutes
Live Selects - initially, only interested in select speeds. should support 500 selects/second. Updates/second will be relatively much smaller number and can be ignored for this exercise.
need 24x7 availability. 2 independent DB servers should be available to serve select calls (with data replicated).

Questions:

At present, I am looking at Oracle. How has your experience been with other commercial (or) opensource solutions for large databases?
What hardware-OS have you seen to work best? I am planning for Linux on Dell.
Is Network storage, such as NetApp, a must? What issues do you foresee with using commercial off the shelf disks?
Once the hardware and OS are ready, how much time would you set aside to setup, configure DB, storage etc.
What team compositions worked best in the environments you have observed? I mean, the various Admins (OS Admin, Oracle DB Admin?) required to manage and operate such a setup. How many of them might be needed to achieve a 24x7 uptime.
Any approximation/range on DB Licenses, Network Storage costs.

I know I dont have all the environment details. I am not looking for exact details, an approximation is sufficient. Though some of the questions might be best answered by managers, I am interested in Admins perspective. I appreciate your input.

First impressions

Depending on your performance requirements, 100TB is a fairly aggressive data volume.  If you want Oracle, you should check out their Exadata systems.  Also, take a look at the offerings from Netezza or Teradata.  With that volume of selects you might want to look at an OLAP based front end or at least fairly aggressive use of materialised views and query rewrite.  You won't get 500 table scans/sec out of anything.For stuff with less stringent latency requirements you might want to consider a larger number of data marts to provide the reporting capacity to your user community.  In this case, SQL Server and SSAS might be an option for the data marts as the licensing on a larger number of servers will be cheaper than trying to do the same with Oracle.
See (1).  Conventional hardware on a shared-disk architecture is likely to be slow on this size data set.
NO! If anybody suggests NFS give them a good kicking.  Either direct attach storage or a multiple controller SAN with lots of mid-range controllers.  Think in terms of maybe a couple of dozen MD3000 series controllers or something similar - if you don't go for a purpose built 'big data' platform.
Get a storage specialist with experience in PB range data warehouse platforms.  You're probably up for a significant ETL development job, and a lot of testing work if you have to meet a stiff SLA.
24x7 on a data warehouse is ambitious at the best of times.  Is this an operational reporting platform?  Perhaps you might elaborate on your requirements a bit.
Sphincter-puckeringly expensive, and dependent on your performance requirements.  Last I saw (a couple of years ago) Netezza used to quote $20,000/TB for TwinFin systems, making your platform $2m for 100TB plus the cost of your redundant server and backup hardware.  Exadata is, I believe, a bit cheaper, but I don't have any pricing to hand.Take a look at Netezza, Exadata and a Teradata platform for comparison, and costings for Ab Initio as an ETL tool.

This is a fairly aggressive set of requirements - 24x7 on a data warehouse isn't normally done and the data volumes are big enough to put you in the realm of a 'big data' platform.  If you have an operational reporting requirement then you should look carefully at what that is.  Keep it separate from your analytics unless you have a specific reason (e.g. a low-latency market data feed) not to.  Mixing operational and analytic requirements on the same platform is bad mojo.

I think you really need to get in specialists to evaluate your requirements.  Without a closer look at what you are trying to achieve all I can give is some empirical suggestions about what to do or not to do.


Scaling out MySQL & Redundancy-Speed tradeoff? I'm building an e-commerce service for a group of sellers. They have a common HQ who manufactures their product.
Tables:

order (id, seller_id, timestamp)
order_products (order_id, product_id, seller_id, timestamp, pincode)
transaction (id, seller_id, timestamp)
transaction_products (transaction_id, product_id, seller_id, timestamp, pincode)
seller (id, pincode, name)
product(id, price)

Specifications:

There are 100 sellers
Each vendor performs 500 transactions per day
Each transaction has 4 products associated with it
Each Vendor places two orders per day to HQ
Each order have 50 products

HQ Requirements:

How many products were sold by which seller in a given month
How many products were sold in a given pincode in a given month
Orders placed by all sellers in a given month

Seller Requirements:

View cost of order placed by him/her (the seller)
View his/her sales of a given month

The product is ready and application works just fine. But, I'm concerned with the two things.

Scaling: Being really new, I don't know much about scaling out or sharding or clustering. How much time have I got until I can keep these aside?
Redundancy: As you can see in transaction_product & order_product, I've reused columns from transaction & order, respectively. The redundant columns are: timestamp, seller_id, pincode. My idea was to avoid joins. But I'm not sure if joins would be more expensive than current redundancy. Can anyone point me in the current direction?

As you grow, keep an eye on the slowlog -- it will help you find the queries that need better indexes and/or reformulation.
No sharding for those small specs.
Clustering (eg, Galera or Innodb Cluster) give a high level of HA, plus some scaling.
Do not fear JOINs.  On the other hand, don't "over-normalize".
If the monthly reports run to slowly, "Summary tables" can fix that.
No PARTITIONing unless you plan to purge "old" data.


MYSQL Trigger update on a table Assuming that I have a table, user. How do I calculate the sum of votes and paymentAmt each time they are being updated using trigger? Then, the votes and paymentAmt will be added to the point. I want to do it for every users (1 and 2) everytime the they are updated without selecting the specific user id.May i know how do i automatically update points for each user? My trigger does not update the table even after I alter the datas

1) user_ID | votes | paymentAmt | point (USER)
    1          10        20        30 <-- automatically updated
    2          25        30        55
    3          10        40        50

Trigger

CREATE Trigger upd_pt
AFTER UPDATE ON user
FOR EACH ROW
BEGIN
    UPDATE user
        set point = (select sum(votes) from user) + user.paymentAmt
        where user.user_ID = new.user_ID;
END

I believe this is what you need:

CREATE Trigger upd_pt
BEFORE UPDATE ON user
FOR EACH ROW
SET NEW.point = NEW.votes + NEW.paymentAmt

This will set points before the user record is updated, so that total will be stored.

Based on code from the MySQL 5.7 Reference Manual.

NOTE: For best results, you probably also want to have this as an INSERT trigger, and may want to adjust if votes or paymentAmt can be NULL.


Sync two mysql database one in intranet and the other on internet I'm making the training and placement module for my college. But I'm facing problems to sync the databases, one that will be used by the students and the other that will be used by the HR of a company. As the students's DB is completely on intranet and is not accessible via internet. As the HR will be using the online version and will be providing information on that. I want to sync the data between both version.

I have read about database replication but that cannot be implemented as these versions cannot be connected.

We cannot use mysqldump as sometimes there will be modifications in the intranet DB that has to be replicated in the online DB.

As the operators of this system don't belong from technical background: I thought of coming with a file based solution where on just click of a button we can download the file and sync up with the intranet version and vica-versa.

But the problem is that I cannot come up with any decent solution till now.
How can I solve this problem?

MySQL has replication cabapilities, sure you has revised the MySQL documentation.

This means that MySQL copies data from master to slave, not both directions.

You proposed "just click a button", if you don't want a "do it yourself" solution maybe can try with a database tool like MySQL workbench.

In any case it is dangerous to synchronize two databases with files: data inconsistencies, primary keys collisions, ...


Aggregate results from select queries I have a table with three columns: id, date, state.

For each unique value of id, I would like to select date and state, multiply the state array by a scalar variable for each id defined from else, and then sum up the state arrays where the date column is the same value.

How would this be achieved in a mysql statement?

Example:

SELECT scal_val FROM table1 WHERE id = 413 into @scal_val;
SELECT date, state * @scal_val as a FROM table2 WHERE id = 413
# now need to loop through ids in table2 for each id, adding state*@scal_val each time

table1:

╭───╥────────────┬─────────────╮
│   ║     id     │  scal_val   │
╞═══╬════════════╪═════════════╡
│ A ║ 413        │ 250         │
│ B ║ 414        │ 50          │
│ C ║ 415        │ 10          │
└───╨────────────┴─────────────┘

table2:

╭───╥────────────┬─────────────┬─────────────╮
│   ║     id     │    date     │    state    │
╞═══╬════════════╪═════════════╪═════════════╡
│ A ║ 413        │ 2016-01-01  │       1     │
│ B ║ 413        │ 2016-01-02  │       0     │
│ C ║ 413        │ 2016-01-03  │       1     │
│ D ║ 414        │ 2016-01-01  │       1     │
│ E ║ 414        │ 2016-01-02  │       1     │
│ F ║ 414        │ 2016-01-03  │       1     │
│ G ║ 415        │ 2016-01-01  │       1     │
│ H ║ 415        │ 2016-01-02  │       0     │
│ I ║ 415        │ 2016-01-03  │       0     │
└───╨────────────┴─────────────┴─────────────┘

result:

╭───╥────────────┬─────────────╮
│   ║   date     │    state    │
╞═══╬════════════╪═════════════╡
│ A ║ 2016-01-01 │ 310         │
│ B ║ 2016-01-01 │ 50          │
│ C ║ 2016-01-01 │ 300         │
└───╨────────────┴─────────────┘

MySQL version 5.6.10

SELECT t2.`date`, SUM(t1.scal_val * t2.state) state
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
GROUP BY t2.`date`


Permissions problem with BULK INSERT We are getting the following error when trying to use BULK INSERT.

  Msg 4861, Level 16, State 1, Line 10 Cannot bulk load because the file
  "\filer\share\loadfile.txt" could not be opened. Operating system
  error code 5(Access is denied.).

When we check the security on the directory everyone is listed as having full access and in fact we have confirmed this by trying to open the file manually as myself, another DBA, one of the devs, and the service account running the instance.  We also get the error when attempting to run the BULK INSERT myself, the other DBA, the dev and a sysadmin SQL login I created to run the test.

The instance is SQL Server 2008 R2

The code is:

   CREATE TABLE #Temp1 (CodeNum VarChar(10), Cost Money)

   BULK INSERT #Temp1
   FROM '\\filer\share\loadfile.txt'
   WITH (
           FIELDTERMINATOR = ',',
           ROWTERMINATOR = ';\n'
           );

   GO

Can anyone give me some direction on where my security issue is?

First, I would check the "effective" permissions of the Service Account used to run the MSSQLSERVER service for that NTFS share. This is mainly to ensure that no "deny"s exist for that account, or any Active Directory group in which that the account exists, in a parent folder that are getting inherited down to the path in question.

Second, I would consider attempting to enable Kerberos Delegation for that service account, as per the Microsoft recommendation for BULK INSERT (see below). Now, I would have expected that granting the service account the appropriate read/execute permissions and using a SQL Server Login would have worked (delegation shouldn't be needed in this case because the logon event is local and the external resource is only a single hop away), BUT since it isn't, some other factor could be getting in the way (perhaps Kerberos and SPNs are required, even when not using Delegation). Either way, it seems like this is a common-enough scenario for Microsoft to have documented it across two MSDN pages.

If you currently are using NTLM instead of Kerberos, you try switching to Kerberos and setting up the necessary SPN first, prior to enabling any account for Delegating, and see if that works.

  Security Account Delegation (Impersonation)

  If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

  When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

  To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
  For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).

And if you follow that link at the end to the "Import Bulk Data..." page, you will see the following section:

  Security considerations

  If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service).

  To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

  For example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process.
  SQL Server and Microsoft Windows can be configured to enable an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as impersonation or delegation. Understanding how SQL Server version handle security for user impersonation is important when you use BULK INSERT or OPENROWSET. User impersonation allows the data file to reside on a different computer than either the SQL Server process or the user. For example, if a user on Computer_A has access to a data file on Computer_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of SQL Server that is running on Computer_C, access the data file on Computer_B, and bulk import data from that file into a table on Computer_C.


SQL Server Logical Device for Directories I am in need of logical backup device option for SQL Server, but not for disk files, but Windows directories. Is there anything like this available or how can I achieve that? Even from 3rd parties?
I have different folders on different machines and I need one logical name for them both, sure one on each server.
I need to create one logical directory in several servers with the same name, but their paths in actuality are different. So I can create a logical backup device with a path, which is like this c:/myfolder/backup.bak and all the backups initiated to this device will be appended in the same file. But I need to create it for the c:/myfolder directory (aka folder) without specifying the file backup.bak. so it will reference to a folder and not to a file, so I can create different backups in the folder with different names and separate files.

I have different folders on different machines and I need one logical
name for them both, sure one on each server.

If you have different folders on different servers and you need them to be presented with the same name you could use mklink to create a directory symbolic link.


Finding average of multiple delta times I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example:

SELECT *,
  "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
FROM "Detection"
WHERE
  "RecognitionTimestamp" >= '2018-10-03 09:01:00' AND
  "RecognitionTimestamp" <= '2018-10-04 09:00:00'

How can I do this?
I've tried avg("delta_previous"), avg(delta_previous),avg("RecognitionTimestamp" - lag("RecognitionTimestamp"))

It's not possible to run aggregate function over an expression involving a window function in the same query level, because aggregate functions are evaluated before window functions. You'd need a subquery like:

SELECT avg(delta_previous) AS avg_delta_previous
FROM (
   SELECT "RecognitionTimestamp"
        - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
   FROM   "Detection"
   WHERE  "RecognitionTimestamp" >= '2018-10-03 09:01:00'
   AND    "RecognitionTimestamp" <  '2018-10-04 09:00:00'  -- typically, you'd want "<" here, not "<="!
   ) sub;

Consider the sequence of events in a SELECT query:

Best way to get result count before LIMIT was applied

But this simpler query should give you the same result, only much cheaper:

SELECT (max("RecognitionTimestamp")
      - min("RecognitionTimestamp")) / (count(*) - 1) AS avg_delta_previous
FROM   "Detection"
WHERE  "RecognitionTimestamp" >= '2018-10-03 09:01:00'
AND    "RecognitionTimestamp" <  '2018-10-04 09:00:00';

Subtract the minimum from the maximum timestamp to get the total interval and divide it by the number or rows - 1. Voilá.


Instead of

SELECT unnest, ordinality
FROM unnest(ARRAY[6,5,4,3,2,1]) WITH ORDINALITY;

you can also write

SELECT unnest, row_number() OVER () AS ordinality
FROM unnest(ARRAY[6,5,4,3,2,1]);

Something like:

...
from s_product
  join (
    select unnest, row_number() over ()
    from unnest(array[....])
  ) as x(shopid, idx)
    on s_product.product_shop_id = x.shopid
....


Oracle: terrible execution times from virtual application server I've been provided with an Oracle VM linux box playing host to an Oracle 11g installation. I've also been provided with an Oracle VM Windows Server 2008 64-bit box acting as an application server. Tasked with deploying my application to this environment, I've created / populated the DB, installed instantclient drivers and SQL*Plus on the application server and ran a few simple queries...

...and performance is terrible. A selection on 9000 rows takes 30 seconds.

N.B. the same SELECT * FROM ... ; query is being used as a baseline, executed from SQL*Plus with timing on, autotrace traceonly statistics and arraysize 5000.

Things I've tried:

HRPING the DB server from the application server - response time is around 0.5ms (slow, but not this slow)
Execute the query using SQL*Plus directly on the DB server - execution time is 0.2 seconds
Execute the query using the exact same instantclient drivers / SQL*Plus client from a different (physical) machine on the network - execution time is 0.2 seconds
Checked that tablespaces are not full
Checked that tmpfs (/dev/shm) space is not full
Check that no AV / firewalls are running

I'm convinced this is an infrastructure / networking issue, but the infrastructure admin insists it is a configuration / software issue.

Other factors:

The application is using manual IPv4 settings, and the DNS server was not specified... The local machine I used for testing had Automatic Settings. I've now requested for the correct details to be applied. Could this cause the issue?
There's an undiagnosed (unsolved) issue whereby if either the application server or the DB is restarted, it drops off the network and requires a shutdown/startup command to be issued via the Oracle VM client to bring it back
The application server came with Oracle PV drivers installed... I uninstalled to see if this would have any impact, but no difference has been observed

I'm all out of ideas. Any suggestions? Thanks in advance.

EDIT:

I'm currently looking into DNS settings. They were missing on the application server, and I'm not sure that they're correct on the DB server in /etc/resolv.conf either. I found a couple of resources suggesting that incorrect DNS settings could cause very poor performance.

This is almost certainly a networking issue (as the execution on a different machine is way faster) so I doubt you'll be able to go much further with any Oracle tools. I've never had any needs to tweak out-of-the-box network parameters to get good performance.

What I suggest is you use a standard network benchmarking tool, e.g. iPerf. It needs to be cross-platform since you have a mixed Linux/Windows environment. Keep in mind that the issue could be due to either low bandwidth or high latency. Run the same test from the other client from which you're getting good SQL Plus results, compare and if it's massively different send the evidence to your networking admin guys.

By the way, if you still need more advice I suggest you post to https://serverfault.com/ as you'll likely to reach more experienced network admins there.


Best way to get last identity inserted in a table Which one is the best option to get the identity value I just generated via an insert? What is the impact of these statements in terms of performance?

SCOPE_IDENTITY()
Aggregate function MAX()
SELECT TOP 1 IdentityColumn FROM TableName ORDER BY IdentityColumn DESC

Use SCOPE_IDENTITY() if you are inserting a single row and want to retrieve the ID that was generated.

CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));

INSERT #a(x) VALUES('a');

SELECT SCOPE_IDENTITY();

Result:

----
1

Use the OUTPUT clause if you are inserting multiple rows and need to retrieve the set of IDs that were generated.

INSERT #a(x)
  OUTPUT inserted.identity_column
  VALUES('b'),('c');

Result:

----
2
3

  and why this is best faster option?

Performance aside, these are the only ones that are guaranteed to be correct in the default isolation level and/or with multiple users. Even if you ignore the correctness aspect, SQL Server holds the inserted value in SCOPE_IDENTITY() in memory, so naturally this will be faster than going and running your own isolated query against the table or against system tables.

Ignoring the correctness aspect is like telling the mailman he did a good job delivering today's mail - he finished his route 10 minutes faster than his average time, the problem is, none of the mail was delivered to the right house.

Do not use any of the following:

@@IDENTITY - since this can't be used in all scenarios, for example when a table with an identity column has a trigger that also inserts into another table with its own identity column - you will get the wrong value back.
IDENT_CURRENT() - I go into detail about this here, and the comments are useful reading as well, but essentially, under concurrency, you will often get the wrong answer.
MAX() or TOP 1 - you would have to protect the two statements with serializable isolation in order to ensure that the MAX() you get isn't someone else's. This is much more expensive than just using SCOPE_IDENTITY().

These functions also fail whenever you insert two or more rows, and need all the identity values generated - your only option there is the OUTPUT clause.


Consolidating large table I need some help with a complex query. Table def:

                                        Table "public.agent_log"
     Column      |           Type           |                          Modifiers
-----------------+--------------------------+-------------------------------------------------------------
 class           | text                     |
 hostname        | text                     |
 ip_address      | text                     |
 promise_handle  | text                     |
 promiser        | text                     |
 promisee        | text                     |
 policy_server   | text                     |
 rowId           | integer                  | not null default nextval('"agent_log_rowId_seq"'::regclass)
 timestamp       | timestamp with time zone |
 promise_outcome | text                     |
Indexes:
    "primary_key" PRIMARY KEY, btree ("rowId")
    "client_by_timestamp" btree ("timestamp", class)

Each line in the table is a log. There will be many rows that are the same except for the timestamp. As the data ages, I want to cull the rows. I want to group records that have the same combination of class, ip_address, and hostname, and keep the highest timestamp for each day from each group. Example:

2014-01-22T13:44:00 any 192.168.0.1 moon.example.com ...
2014-01-22T14:44:00 any 192.168.0.1 moon.example.com ... KEEP
2014-01-22T14:44:00 any 192.168.0.2 mars.example.com ... KEEP
2014-01-22T13:44:00 any 192.168.0.2 mars.example.com ...
2014-01-23T13:44:00 any 192.168.0.1 moon.example.com ...
2014-01-23T14:44:00 any 192.168.0.1 moon.example.com ... KEEP
2014-01-23T14:44:00 any 192.168.0.2 mars.example.com ... KEEP
2014-01-23T13:44:00 any 192.168.0.2 mars.example.com ...
2014-01-22T13:44:00 cpu_1 192.168.0.1 moon.example.com ...
2014-01-22T14:44:00 cpu_1 192.168.0.1 moon.example.com ... KEEP
2014-01-22T14:44:00 cpu_1 192.168.0.2 mars.example.com ... KEEP
2014-01-22T13:44:00 cpu_1 192.168.0.2 mars.example.com ...
2014-01-23T13:44:00 cpu_1 192.168.0.1 moon.example.com ...
2014-01-23T14:44:00 cpu_1 192.168.0.1 moon.example.com ... KEEP
2014-01-23T14:44:00 cpu_1 192.168.0.2 mars.example.com ... KEEP
2014-01-23T13:44:00 cpu_1 192.168.0.2 mars.example.com ...

I'm testing with this, note that I'm using select instead of delete for this test. I can't get the where correct and the few things I've tried resulted in a very long query. Any suggestions for improvement or alternate approaches welcome.

explain SELECT * FROM agent_log
WHERE
NOT IN (
   SELECT
          class,
          max(timestamp) as timestamp,
          hostname,
          ip_address,
          promise_handle,
          promiser,
          promisee,
          policy_server,
          promise_outcome
      FROM agent_log
      WHERE timestamp < now() - interval '7 days'
      GROUP BY
          class,
          DATE_TRUNC( 'day', timestamp),
          hostname,
          ip_address,
          promise_handle,
          promiser,
          promisee,
          policy_server,
          promise_outcome
)
AND timestamp < now() - interval '7 days'

I want to group records that have the same combination of class,
  ip_address, and hostname, and keep the highest timestamp for each day
  from each group.

Not using the column name timestamp (like you shouldn't either). It's a reserved word in SQL and a basic type name in Postgres. Using ts instead.

The query is surprisingly simple with DISTINCT ON:

SELECT DISTINCT ON (class, ip_address, hostname, ts::date) *
FROM   agent_log
WHERE  ts < now() - interval '7 days'
ORDER  BY class, ip_address, hostname, ts::date, ts DESC;

Detailed explanation:
Select first row in each GROUP BY group?


Login failed for User 18456 State 5. User id works on other machines I have a really weird login issue I've been pursuing. I experience this only on my Macbook Pro (2018) and only when logging into this one specific SQL Server 2016 database directly through a database client.

Login attempts through any database client to this specific database are met with:

  Login failed for user ''. (Msg 18456, Level 14).

Failure state is 5, "invalid user id".

These credentials work fine when used as credentials for a local copy of the java application that this DB backs.

These credentials work fine when used to login from another machine.

User ID/password does not work only when used to log in through a database client, and only on my machine. Tested with DataGrip and SQLPro for SQL Server. Both applications can log into other SQL Server databases without issues.

I generated a new user account to no avail. The issue persists. I can connect to other database servers through 1433, and my application can connect to this database when running locally with the same credentials

I am connected through a VPN, but I don't think that has anything to do with it.

I would suspect that it's some kind of encoding issue with the request, but the problem persists across multiple applications.

Anybody have any ideas?

According to Microsoft documentation it could be:

  The error reason An attempt to login using SQL authentication failed.
  Server is configured for Windows authentication only. can be returned
  in the following situations.

  When the server is configured for mixed mode authentication, and an ODBC connection uses the TCP protocol, and the connection does not
  explicitly specify that the connection should use a trusted
  connection.
  When the server is configured for mixed mode authentication, and an ODBC connection uses named pipes, and the credentials the client
  used to open the named pipe are used to automatically impersonate the
  user, and the connection does not explicitly specify that the
  connection should use a trusted connection.

  To resolve this issue, include TRUSTED_CONNECTION = TRUE in the
  connection string.


How to remove a listener IP from a SQL Availability Group Listener by T-SQL The title says it all.
I have an availability group that was a multiple subnet, but now the environment have changed ,all replicas exist within a single subnet.
How to remove the superfluous second IP.
Is this only way remove the ip from Failover Cluster Manager?
I have submited a feedback to micosoft,welcome to vote.

https://connect.microsoft.com/SQLServer/feedback/details/2907012/how-do-you-remove-a-listener-ip-from-a-sql-availability-group-listener

Based on my experience with SQL Server 2014 and 2016, you can simply remove the IP address resource in Failover Cluster Administrator.  We have done this on several non-production clusters and on a production cluster in order to move from one subnet to the other.  I stopped the SQL Server service on all nodes to ensure that there would be no connections and less chance of some sort of conflict since it's a bit out of the ordinary.

The other option is to simply take a few minutes of downtime to remove the listener and rebuild it.  If you scripted all of the operations it should only take seconds.


How to get all the data before the current date and data for the next 3 days from current date.DATEDIFF(now(),d.tentative_date_of_return) <= now() + 3 SELECT DISTINCT
d.name AS "ID:Link/Demo Request:120",
d.requester_name AS "Requester Name:Data:190",
d.customer_name AS "Customer Name:Data:250",
d.date_of_request AS "Date Of Request:Date:120",
d.tentative_date_of_return AS "Tentative Date Of Return:Date:170",
d.material_sent_from_location AS "Material Sent From Location:Data:190",
d.delivery_note AS "Delivery Note:Data:100",
d.material_status AS "Material Status:Data:120",
d.product_supplied_condition AS "Product Supplied Condition:Data:150",
d.courier_service AS "Courier Service:Data:120",
d.shipped_date_from_rahi AS "Shipped Date From Rahi:Date:120",
d.tracking_no AS "Tracking No:Data:120",
DATEDIFF(now(),d.tentative_date_of_return) AS "No Of Days:Data:100"

FROM
`tabDemo Request` as d

WHERE
d.material_status not like "Recieved From Customer"
AND
DATEDIFF(now(),d.tentative_date_of_return) <= now() + 3

ORDER BY d.tentative_date_of_return ASC

You want

AND d.tentative_date_of_return <= CURDATE() + INTERVAL 3 DAY


MySQL error 1593 on replication between 5.5 on WIndows and 5.6 on CentOS 7 I'm setting up replication between the live server (running Windows 2008 R2 and MySQL 5.5.19) and a dedicated replication server (running CentOS 7 and MySQL 5.6.30). The CentOS box does have MySQL not MariaDB.

In the master my.ini, I've set:

server-id=131025185082

In the slave my.cnf, I've set:

server-id=192168001182

The server is generating a mysql-bin and I've configured the slave to connect. But; I'm getting a 1593 error:

Fatal error: The slave I/O thread stops because master and slave have  equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). The log position given is the start point from SHOW MASTER STATUS on the master.

When I do:

show variables like 'server_id';

on both machines, they both come back with:

+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| server_id     | 4294967295 |
+---------------+------------+

Can anyone suggest what's gone wrong here?

Thanks.

If both the servers showing same server-id, you can set this to some other value and check again.

On Master:

server-id = 1

On Slave:

server-id = 2

Make sure that, while giving change master settings, give the host name as ip address of the master server. This should work, check again and see if the same issue exists. I hope this will help you.


How to remove unreferenced rows in a "Many to One" relation? in my db i have the following entities: Cities, Persons, Houses.

Each Person MUST live in a City.
Each Person CAN live in a House.
Each Person can change or leave their Houses.
Persons living in the same house must also live in the same city.
Empty Houses must be destroyed.

Here's the schema:

How can I make sure that Persons living in the same House they also live in the same City?
How to destroy empty Houses? Is there a solution other than triggers?

I was thinking about a different approach:

I know it seems a stupid solution but i can use ON DELETE CASCADE on the House, so no House will remain unreferenced after City removal.

What do you think is the best way to solve this?

Thanks in advance an sorry for my english.

A Person and a House must be associated with a City, but a Person does not necessarily have to be associated with a House. If a Person is associated with a House, they must be associated with the same City.

Your second diagram is correct. Both Person and House are independently associated with City. You just need another table.

Simple, you create a type of intersection table to relate a Person to a House that also forces Person and House to refer to the same city.

create table PersonHouse(
    PersonID int not null,
    HouseID  int not null,
    CityID   int not null,
    constraint PK_PersonHouse primary key( PersonID ),
    constraint FK_PersonHouse_Person foreign key( PersonID, CityID )
        references Person( ID, CityID ),
    constraint FK_PersonHouse_House foreign key( HouseID, CityID )
        references House( ID, CityID )
);

PersonID is the PK so a Person cannot reside in more than one House but the same House may contain more than one Person.

When inserting a record into PersonHouse with Person 15 who (in Person table) is associated with City 37, the House ID must be contained in the House table and must also be associated with City 37. Any attempt to enter a Person and House associated with different cities will generate a foreign key violation.